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?
