How to load OPENJSON from Dynamic Nodes in SQL Server

Question

The JSON format I'm receiving via REST API has dynamic nodes which I am unable to load into OPENJSON. The JSON response is from a third party and I am unable to alter it. See example below and notice the dates are being used as nodes:

{
    "Meta Data": {
        "1. Information": "API JSON",
        "2. TYPE": "JSON",
    },
    "TSD": {
        "2019-08-13": {
            "value1": "136.0500",
            "value2": "137.7400"
        },
        "2019-08-12": {
            "value1": "137.0700",
            "value2": "137.8600"

        },
        "2019-08-09": {
            "value1": "138.6100",
            "value2": "139.3800"
        }
    }
}

I am able to grab a specific node if I know the exact date using the following code:

 SELECT [value1], [value2]
 FROM OPENJSON(@json, '$."TSD"."2019-08-13"')
 WITH (
    [value1] numeric(20,10),
    [value2] numeric(20,10),
    )

This however does not help, as I will not know the dates in advance and can only select one date node at a time with this method.

How do I reference these dynamic dates without knowing their node names in advance?

Answer 1

You need to call OPENJSON() twice. The first call is with default schema and the result is a table with key, value and type columns (the key column holds dates). The second call is with explicit schema with defined columns.

Note, that you need to remove the extra , after "2. TYPE": "JSON".

JSON:

DECLARE @json nvarchar(max) = N'{
    "Meta Data": {
        "1. Information": "API JSON",
        "2. TYPE": "JSON"
    },
    "TSD": {
        "2019-08-13": {
            "value1": "136.0500",
            "value2": "137.7400"
        },
        "2019-08-12": {
            "value1": "137.0700",
            "value2": "137.8600"

        },
        "2019-08-09": {
            "value1": "138.6100",
            "value2": "139.3800"
        }
    }
}'

Statement:

SELECT 
   j1.[key] AS [Date],
   j2.value1,
   j2.value2
FROM OPENJSON(@json, '$.TSD') j1
CROSS APPLY OPENJSON(j1.[value])  WITH (
   value1 numeric(20, 4) '$.value1',
   value2 numeric(20, 4) '$.value2'
) j2

Output:

-------------------------------
Date        value1      value2
-------------------------------
2019-08-13  136.0500    137.7400
2019-08-12  137.0700    137.8600
2019-08-09  138.6100    139.380