Parse unknown JSON path in TSQL with openjson and/or json_value

Question

I have a incoming data structure that looks like this:

declare @json nvarchar(max) = '{
    "action": "edit",
    "data": {
        "2077-09-02": {
            "Description": "some stuff",
            "EffectDate": "2077-1-1"
        }
    }
}';

To give you a long story short, I think TSQL hates this json structure, because no matter what I have tried, I can't get to any values other than "action".

The {data} object contains another object, {2077-09-02}. "2077-09-02" will always be different. I can't rely on what that date will be.

This works:

select json_value(@json, '$.action');

None of this works when trying to get to the other values.

select json_value(@json, '$.data');  --returns null

select json_value(@json, '$.data[0]'); --returns null

select json_value(@json, 'lax $.data.[2077-09-02].Description');
--JSON path is not properly formatted. Unexpected character '[' is found at position 11.

select json_value(@json, 'lax $.data.2077-09-02.Description');
--JSON path is not properly formatted. Unexpected character '2' is found at position 11.

How do I get to the other values? Is the JSON not perfect enough for TSQL?

Gottfried Lesigang · Accepted Answer · 2019-05-23 15:44:23Z

2

It is never a good idea to use the declarative part of a text based container as data. The "2077-09-02" is a valid json key, but hard to query.

You can try this:

declare @json nvarchar(max) = '{
    "action": "edit",
    "data": {
        "2077-09-02": {
            "Description": "some stuff",
            "EffectDate": "2077-1-1"
        }
    }
}';

SELECT A.[action]
      ,B.[key] AS DateValue
      ,C.*
FROM OPENJSON(@json)
WITH([action] NVARCHAR(100)
    ,[data]   NVARCHAR(MAX) AS JSON) A
CROSS APPLY OPENJSON(A.[data]) B 
CROSS APPLY OPENJSON(B.[value]) 
WITH (Description NVARCHAR(100)
     ,EffectDate DATE) C;

The result

action  DateValue   Description EffectDate
edit    2077-09-02  some stuff  2077-01-01

The idea:

The first OPENJSON will return the action and the data.
I use a WITH clause to tell the engine, that action is a simple value, while data is nested JSON
The next OPENJSON dives into data
We can now use B.[key] to get the json key's value
Now we need another OPENJSON to dive into the columns within data.

However: If this JSON is under your control I'd suggest to change its structure.

answered May 23, 2019 at 15:44

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

1 Comment

NamedArray Over a year ago

this is some high-level SQL, my friend, thank you!!! The structure you see is from DataTable's Editor. The {data: ""} object could contain multiple objects, which represent a row that's being modified; the hard-to-query key represents the row actively being modified. It's the HTML id of the row, AND the primary key in the table. Long Story Short, YES, I can manually manipulate the structure and make it FLAT --- but with this, you have taught me something new, and now I can leave the original structure intact if I choose to do so, thus saving extra lines of JavaScript.

Panagiotis Kanavos · Accepted Answer · 2019-05-23 14:49:54Z

2

Use double quotes instead of []. JSON Path uses JavaScript's conventions where a string is surrounded by double quotes. The documentation's example contains this path $."first name".

In this case :

select json_value(@json,'$.data."2077-09-02".Description');

Returns :

some stuff

As for the other calls, JSON_VALUE can only return scalar values, not objects. You need to use JSON_QUERY to extract JSON objects, eg :

select json_query(@json,'$.data."2077-09-02"');

Returns :

{
    "Description": "some stuff",
    "EffectDate": "2077-1-1"          
}

answered May 23, 2019 at 14:49

Panagiotis Kanavos

134k16 gold badges211 silver badges270 bronze badges

1 Comment

NamedArray Over a year ago

Oh man, that's great! How do I reference the path to the object if I don't know what the root date will be?

Collectives™ on Stack Overflow

Parse unknown JSON path in TSQL with openjson and/or json_value

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related