January 11, 2025 at 1:14 am
I am using the following code to read a json file. I can display the keys and coreentitykey info. However, I am unable to view the attributes (Enable and Name). Not sure how to display with the other fields.
Any help is greatly appreciated.
DECLARE @json NVARCHAR(MAX) = N'[
{
"metadata": {
"numNodes": "1329",
"metadataKey": "234gffert-456-ff34",
"cacheKey": "23dfrg6-ghj-78j",
"currencyCode": "USD",
"totalNodes": "1329",
"totalElements": "1329"
},
"data": {
"key": {
"ROOT": "-1"
},
"coreEntityKey": {},
"attributes": [],
"children": [
{
"key": {
"CODE": "153256",
"ID1": "565656"
},
"coreEntityKey": {
"DEPT": {
"id": "2222",
"qualifier": "Site A"
},
"ZCODE": {
"id": "555",
"qualifier": "Hours1"
},
"CUST": {
"id": "66666"
},
"CDEP": {
"id": "1111"
}
},
"attributes": [
{
"key": "ENABLE",
"rawValue": "false",
"value": "False"
},
{
"key": "NAME",
"rawValue": "aaaa, bbbb",
"value": "aaaa, bbbb"
}
]
}
]
}
}
]';
SELECT
JSON_VALUE(data.value, '$.key.CODE') AS CODE,
JSON_VALUE(data.value, '$.key.ID1') AS ID1,
JSON_VALUE(data.value, '$.coreEntityKey.DEPT.id') AS DEP_ID,
JSON_VALUE(data.value, '$.coreEntityKey.DEPT.qualifier') AS DEP_DE,
JSON_VALUE(data.value, '$.coreEntityKey.ZCODE.id') AS ZCODE,
JSON_VALUE(data.value, '$.coreEntityKey.ZCODE.qualifier') AS ZCODE_DES,
JSON_VALUE(data.value, '$.coreEntityKey.CUST.id') AS CUST_ID,
JSON_VALUE(data.value, '$.coreEntityKey.CDEP.id') AS CDEP
FROM OPENJSON(@json) AS root
CROSS APPLY OPENJSON(root.value, '$.data.children') AS data
January 11, 2025 at 4:13 am
The contents of "attributes" is an array, so you must further shred each row by passing the "attributes" array into another OPENJSON call through OUTER APPLY. This produces one row for each set of attribute.key, attribute.rawValue, and attribute.value values.
SELECT
JSON_VALUE(data.value, '$.key.CODE') AS CODE,
JSON_VALUE(data.value, '$.key.ID1') AS ID1,
JSON_VALUE(data.value, '$.coreEntityKey.DEPT.id') AS DEP_ID,
JSON_VALUE(data.value, '$.coreEntityKey.DEPT.qualifier') AS DEP_DE,
JSON_VALUE(data.value, '$.coreEntityKey.ZCODE.id') AS ZCODE,
JSON_VALUE(data.value, '$.coreEntityKey.ZCODE.qualifier') AS ZCODE_DES,
JSON_VALUE(data.value, '$.coreEntityKey.CUST.id') AS CUST_ID,
JSON_VALUE(data.value, '$.coreEntityKey.CDEP.id') AS CDEP,
JSON_VALUE(attrs.value, '$.key') AS AttributeKey,
JSON_VALUE(attrs.value, '$.rawValue') AS AttributeRawValue,
JSON_VALUE(attrs.value, '$.value') AS AttributeValue,
JSON_QUERY(data.value, '$.attributes') AS [ThisIsWhatGetsShreddedInTheOUTERAPPLY]
FROM OPENJSON(@json) AS root
CROSS APPLY OPENJSON(root.value, '$.data.children') AS data
OUTER APPLY OPENJSON(JSON_QUERY(data.value, '$.attributes')) as attrs
Eddie Wuerch
MCM: SQL
January 11, 2025 at 11:58 pm
Thanks for the reply. Much appreciated. This works, but returns 2 rows. This sample json got only one row.
January 12, 2025 at 3:44 pm
In the function JSON_VALUE 'path' is a required parameter and in the tvf OPENJSON 'path' is optional. The implementation of 'json path' in SQL Server is a limited subset of JSONPath the expression language. When specifying the JSON 'path' in tsql there's a limited ability to use array index selectors. Array indexers could simplify the code and flatten the JSON. The correct JSON path expression could eliminate the need for the FROM clause
SELECT
JSON_VALUE(@json, '$[0].data.children[0].key.CODE') AS CODE,
JSON_VALUE(@json, '$[0].data.children[0].key.ID1') AS ID1,
JSON_VALUE(@json, '$[0].data.children[0].coreEntityKey.DEPT.id') AS DEP_ID,
JSON_VALUE(@json, '$[0].data.children[0].coreEntityKey.DEPT.qualifier') AS DEP_DE,
JSON_VALUE(@json, '$[0].data.children[0].coreEntityKey.ZCODE.id') AS ZCODE,
JSON_VALUE(@json, '$[0].data.children[0].coreEntityKey.ZCODE.qualifier') AS ZCODE_DES,
JSON_VALUE(@json, '$[0].data.children[0].coreEntityKey.CUST.id') AS CUST_ID,
JSON_VALUE(@json, '$[0].data.children[0].coreEntityKey.CDEP.id') AS CDEP,
JSON_VALUE(@json, '$[0].data.children[0].attributes[0].key') AS attribute_01_key,
JSON_VALUE(@json, '$[0].data.children[0].attributes[1].key') AS attribute_02_key;
If you don't know the array offset indexes, then the (better) alternative could be to add schema definition(s) to OPENJSON using the WITH clause
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 13, 2025 at 4:44 pm
Thanks for the response. Much appreciated.
I was able to modify the script to make it work to give me one row. However, this script works only when the json contents are not within square brackets. When there are square brackets, it returns 0 rows.
Any way to make this code wirks with square brackets?
Select
JSONData.CODE,
JSONData.ID1,
JSONData.CUST,
JSONData.ENABLE,
JSONData.NAME
FROM OPENJSON(@json, '$.data.children')
WITH (
CODE NVARCHAR(20) '$.key.CODE',
ID1 NVARCHAR(20) '$.key.ID1',
CUST NVARCHAR(20) '$.coreEntityKey.CUST.id',
ENABLE NVARCHAR(20) '$.attributes[0].value',
NAME NVARCHAR(20) '$.attributes[1].value'
) as JSONData;
January 14, 2025 at 3:18 pm
In your JSON the outermost delimiter is an array bracket which means it's an anonymous array. To reference the only record in the array you could provide the 'path' to the 0-th offset element. To make your code work change the FROM clause to this
FROM OPENJSON(@json, '$[0].data.children')
Your original code had 2 references to OPENJSON in the FROM Clause. The first opened the anonymous array and the 2nd provided a path to the data.children subarray
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply