Using TSQL OPENJSON how do I extract a value from JSON array with a dynamic key name

Question

I have a json document with an internal array of attributes. On one of these attributes, the key name changes dynamically/randomly. I can easily extract all the data points except for this last pesky attribute. All the methods I have found or used in the past with OPENJSON relied on the key name being known.

Inside the the "inner" array, the first attribute will have a key name that changes. I want to extract the value associated with that dynamic key, without knowing exactly what that key will be. Hopefully the code below will describe the problem better than I can with words.

Here is what the JSON document looks like formatted for readability ...

{
    "outer1": {
        "inner1": {
            "dynamicKey123": "attribute1",
            "staticKey1": "attribute2",
            "staticKey2": "attribute3",
            "staticKey3": "attribute4"
        }
    },
    "outer2": {
        "inner2": {
            "dynamicKeyABC": "attribute1",
            "staticKey1": "attribute2",
            "staticKey2": "attribute3",
            "staticKey3": "attribute4"
        }
    }
}

Some code to test with ...

CREATE TABLE openjson_test (json_col VARCHAR(MAX));

INSERT INTO openjson_test (json_col)
VALUES ('{"outer1":{"inner1":{"dynamicKey123":"attribute1","staticKey1":"attribute2","staticKey2":"attribute3","staticKey3":"attribute4"}},"outer2":{"inner2":{"dynamicKeyABC":"attribute1","staticKey1":"attribute2","staticKey2":"attribute3","staticKey3":"attribute4"}}}');

The query I have developed so far with troublesome parts commented out ...

SELECT
    json_col,
    so.[key] AS soKey,
    si.[key] AS siKey,
    si.[value] AS siValue,
    --ar.dynamicKey, 
    ar.staticKey1,
    ar.staticKey2,
    ar.staticKey3
FROM openjson_test
CROSS APPLY OPENJSON(json_col) so
CROSS APPLY OPENJSON(json_col, '$.' + so.[key]) si
CROSS APPLY OPENJSON(json_col, '$.' + so.[key] + '.' + si.[key])
WITH (
    --dynamicKey VARCHAR(256) '$.dynamicKey???', How do I extract this value without knowing the key
    staticKey1 VARCHAR(256) '$.staticKey1',
    staticKey2 VARCHAR(256) '$.staticKey2',
    staticKey3 VARCHAR(256) '$.staticKey3'
) ar

score 0 · Answer 1 · answered Apr 17 '19 at 04:55

You can use OPENJSON without the WITH clause and filter out the column with known names:

SELECT json_col,
    so.[key] AS soKey,
    si.[key] AS siKey,
    si.[value] AS siValue,
    ar2.Value AS dynamicKey,
    ar.staticKey1,
    ar.staticKey2,
    ar.staticKey3
FROM dbo.openjson_test t
CROSS APPLY OPENJSON(t.json_col) so
CROSS APPLY OPENJSON(json_col, '$.' + so.[key]) si
CROSS APPLY OPENJSON(json_col, '$.' + so.[key] + '.' + si.[key])
WITH (
    staticKey1 VARCHAR(256) '$.staticKey1',
    staticKey2 VARCHAR(256) '$.staticKey2',
    staticKey3 VARCHAR(256) '$.staticKey3'
) ar
CROSS APPLY (
    SELECT *
    FROM OPENJSON(json_col, '$.' + so.[key] + '.' + si.[key])
    WHERE [Key] NOT IN ('staticKey1','staticKey2','staticKey3')
) ar2

@K_OFLYNN: Please upvote the answers you find useful (see https://meta.stackexchange.com/q/173399/224749). Thank you and welcome to StackOverflow! — Razvan Socol, Apr 17 '19 at 16:12

score 0 · Accepted Answer · answered Apr 17 '19 at 06:23

I'd suggest an approach with conditional aggregation

SELECT
    so.[key] AS soKey,
    si.[key] AS siKey,
    MAX(CASE WHEN attr.[key] NOT IN('staticKey1','staticKey2','staticKey3') THEN attr.[value] END) AS DynamicAttr,
    MAX(CASE WHEN attr.[key]='staticKey1' THEN attr.[value] END) AS attrKey1,
    MAX(CASE WHEN attr.[key]='staticKey2' THEN attr.[value] END) AS attrKey2,
    MAX(CASE WHEN attr.[key]='staticKey3' THEN attr.[value] END) AS attrKey3
FROM openjson_test
CROSS APPLY OPENJSON(json_col) so
CROSS APPLY OPENJSON(json_col, '$.' + so.[key]) si
CROSS APPLY OPENJSON(json_col, '$.' + so.[key] + '.' + si.[key]) attr
GROUP BY so.[key],si.[key];

This technique is used in PIVOT scenarios but allows for a more generic logic.

Thanks @Shnugo. Very clean solution – K_OFLYNN Apr 17 '19 at 08:31 — K_OFLYNN, Apr 17 '19 at 08:31

Using TSQL OPENJSON how do I extract a value from JSON array with a dynamic key name

2 Answers2