Return all values from objects using OPENJSON()

Here is my problem: My JSON may vary based on API response and there is no guarantee that “shortTitle”, for example, can be present in my JSON next time when there is a post request. I have to figure how to “loop” (if that’s the correct term) through JSON to get all fieldID and contents without losing any data.

Here is the query:

DECLARE
// hardcoded json to simulate a post request
@json nvarchar(max) = '{
    "itemTypeID": 13,
    "authorRights": {
        "fieldID": 15,
        "content": "abcd"
    },
    "language": {
        "fieldID": 7,
        "content": "abcd"
    },
    "url": {
        "fieldID": 13,
        "content": "abcd"
    },
    "shortTitle": {
        "fieldID": 8,
        "content": "abcd"
    },
    "data": {
        "fieldID": 6,
        "content": "2022-03-02"
    },
    "summary": {
        "fieldID": 2,
        "content": "abcd"
    },
    "subject": {
        "fieldID": 60,
        "content": "abcd"
    }
}';

BEGIN

SELECT* FROM OPENJSON(@json)
WITH(
fieldID bigint '$.data.fieldID',
content nvarchar(255) '$.data.content'
)

END;

This query returns fieldID and content only for “data” (results). How can I return fieldID and content for all objects?

Answer

If I understand the structure of the input JSON correctly, you need to parse only nested JSON objects. So, using OPENJSON() with default schema and an additional APPLY operator is a possible solution to your problem. As is explained in the documentation, the OPENJSON() function with default schema returns a table with columns key, value and type, and the type column holds an int value that contains the type of the returned value.

SELECT j1.[key], j2.fieldID
FROM OPENJSON(@json) j1
CROSS APPLY OPENJSON (j1.[value]) WITH (fieldID bigint '$.fieldID') j2
WHERE j1.[type] = 5

Result:

key          fieldID
---------------------
authorRights 15
language     7
url          13
shortTitle   8
data         6
summary      2
subject      60