Multiple Json Objects Imported into SQL Server Table

Question

I am trying to Import the Currency Data into a table.

I am able to import one of the data objects by specifying FROM OPENJSON (@JSON, '$.currency."0"') however I am struggling to find a solution which helps me gather all 5 currencies into there own column.

I want to use the unique JSON Object headers for each currency as a data Item (DefaultCurrencyCode)

This is the JSON Data:

"currency": {
    "0": {
      "ISOCode": "USD",
      "name": "US Dollar",
      "symbol": "$",
    },
    "1": {
      "ISOCode": "IEP",
      "name": "Irish Pound or Punt",
      "symbol": "£",
    },
    "2": {
      "ISOCode": "BEF",
      "name": "Belgian Franc",
      "symbol": "₣",
    },
    "3": {
      "ISOCode": "ZAR",
      "name": "South African Rand",
      "symbol": "R",
    },
    "4": {
      "ISOCode": "JPY",
      "name": "Japanese Yen",
      "symbol": "¥",
    }
   }

This is my SQL Server code:

DECLARE @JSON VARCHAR(MAX)

SELECT @JSON = BulkColumn
FROM OPENROWSET 
(BULK 'C:\dev\src\Client\config\common\config.json', SINGLE_CLOB) 
AS j

SELECT ISOCode, name, symbol ,DefaultCurrencyCode
INTO TestTable2
  FROM OPENJSON (@JSON, '$.currency."0"')
  WITH (
    ISOCode VARCHAR(10),
    name VARCHAR(50),
    symbol VARCHAR(50),
    DefaultCurrencyCode INT)

    Select * From TestTable2

This is my current output, however I am trying to get all of them.

See if this helps. learn.microsoft.com/en-us/sql/t-sql/functions/… It looks like you need to remove the , '$.currency."0"' in the open and format the columns in the WITH section. — SS_DBA
– SS_DBA, Commented Jul 30, 2019 at 14:02

Zhorov · Accepted Answer · 2019-07-31 06:35:45Z

You need an additional OPENJSON call with explicit schema to get data from nested JSON objects.

JSON:

DECLARE @json nvarchar(max) = N'{
   "currency": {
    "0": {
      "ISOCode": "USD",
      "name": "US Dollar",
      "symbol": "$"
    },
    "1": {
      "ISOCode": "IEP",
      "name": "Irish Pound or Punt",
      "symbol": "£"
    },
    "2": {
      "ISOCode": "BEF",
      "name": "Belgian Franc",
      "symbol": "₣"
    },
    "3": {
      "ISOCode": "ZAR",
      "name": "South African Rand",
      "symbol": "R"
    },
    "4": {
      "ISOCode": "JPY",
      "name": "Japanese Yen",
      "symbol": "¥"
    }
   }
}'

Statement:

SELECT j2.*
FROM OPENJSON(@json, '$.currency') j1
CROSS APPLY OPENJSON(j1.[value]) WITH (
    ISOCode NVARCHAR(10),
    name NVARCHAR(50),
    symbol NVARCHAR(50),
    DefaultCurrencyCode INT
) j2

Output:

-------------------------------------------------------
ISOCode name                symbol  DefaultCurrencyCode
-------------------------------------------------------
USD     US Dollar           $   
IEP     Irish Pound or Punt £   
BEF     Belgian Franc       ₣   
ZAR     South African Rand  R   
JPY     Japanese Yen        ¥

Collectives™ on Stack Overflow

Multiple Json Objects Imported into SQL Server Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related