JSON Object Data inserted into SQL Server table

Question

I am trying grab the JSON object head for each currency and insert it into a SQL Server table.

This is the code I am using, however I get null values:

DECLARE @json NVARCHAR(MAX) = N'{
   "currency": {
    "0": {
      "ISOCode": "USD",
      "name": "US Dollar",
      "symbol": "$",
      "real": "$#,###.##",
      "free": "#,###.##"
    },
    "1": {
      "ISOCode": "IEP",
      "name": "Irish Pound or Punt",
      "symbol": "£",
      "real": "£#,###.##",
      "free": "#,###.##"
    },
    "2": {
      "ISOCode": "BEF",
      "name": "Belgian Franc",
      "symbol": "₣",
      "real": "#,###.##₣",
      "free": "#,###.##"
     }
   }
}'

SELECT j2.* 
INTO #TempCurrencyTable
FROm OPENJSON(@json, '$.currency') j1
CROSS APPLY OPENJSON(j1.[value]) WITH (
    currency INT
) j2

SELECT * 
FROM #TempCurrencyTable

I get a null value and can't seem to get the header values.

What do you mean header? JSON has no headers or columns, only arrays and dictionaries. The string you posted has a Currency dictionary with elements strangely named 0, 1 etc. Each of those elements is a dictionary with its own attributes. Nothing forces those dictionaries to have the same attributes as the others — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 31, 2019 at 8:08
@PanagiotisKanavos Apologies - possibly using the wrong terminology , I need to grab the "0" , "1" and "2" values and insert them into a data table. — GrahamNorton
– GrahamNorton, Commented Jul 31, 2019 at 8:10
It's not a matter of terminology. If you expect a "header" when there's none, you'll get nothing. "0" and "1" are the keys of the currency dictionary and appear in j1.key. The j1.value column contains the inner objects, which have no currency attribute. You can write just SELECT j1.key FROM OPENJSON(@json, '$.currency') j1 to get the key — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 31, 2019 at 8:16

Zhorov · Accepted Answer · 2019-07-31 09:03:38Z

2

Solution:

What you need here is the next statement:

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

Explanations:

When you use ... OPENJSON(@json, '$.currency') j1 ... you execute OPENJSON() with default schema and the result is a table with columns key, value and type. When you use ... OPENJSON(j1.[value]) WITH ( .. ) .. you execute OPENJSON() with explicit schema and the result is a table with columns defined in your schema.

The documentation about OPENSON() return value is here.

edited Jul 31, 2019 at 9:03

answered Jul 31, 2019 at 8:36

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Panagiotis Kanavos · Accepted Answer · 2019-07-31 08:36:46Z

A JSON string contains only arrays and objects (dictionaries). It has no header. This JSON string contains a top-level object/dictionary with a single element with key value currency. That element is a dictionary with key values "0", "1" etc. Each of those elements contains another boject . Nothing forces those elements to have the same keys

OPENJSON returns the key/value elements of a JSON dictionary as key and value columns. This means that the currency keys will appear in the key column. The value column will hold the inner dictionary.

WITH can be used to parse the value and extract specific attributes as columns. It can't be used to parse the key

This query :

SELECT *
FROm OPENJSON(@json, '$.currency') j1

Returns :

key value                       type
0   { "ISOCode": "USD",  ... }  5
1   { "ISOCode": "IEP",  ... }  5
2   { "ISOCode": "BEF",   ... } 5

Keys are always strings. To convert the key to an integer a CASR or PARSE is needed :

SELECT cast([key] as int)
FROm OPENJSON(@json, '$.currency') j1

The second OPENJSON can be used to parse the currency objects. Without WITH, a new row will be returned for every attribute. WITH can be used to expose the properties as columns. This query :

SELECT 
    cast(j1.[key] as int) ID,
    j2.*
FROm OPENJSON(@json, '$.currency') j1 
CROSS APPLY OPENJSON(j1.[value])  
        with (  ISOCode nvarchar(3), 
                [name] nvarchar(30),
                symbol nvarchar(3),
                [real] nvarchar(10),
                free nvarchar(10)) j2

Returns :

ID  ISOCode name                symbol  real        free
0   USD     US Dollar           $       $#,###.##   #,###.##
1   IEP     Irish Pound or Punt £       £#,###.##   #,###.##
2   BEF     Belgian Franc       ₣       #,###.##₣   #,###.##

Collectives™ on Stack Overflow

JSON Object Data inserted into SQL Server table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related