0
Follow
0
View

Return all values from objects using OPENJSON()

sync3939 注册会员
2023-01-25 08:47

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
donglei55 注册会员
2023-01-25 08:47

Hi you must explicitly enumerate all your desired columns:

SELECT* FROM OPENJSON(@json)
WITH(
  fieldID bigint '$.authorRights.fieldID',
  content nvarchar(255) '$.authorRights.content',
  data_fieldID bigint '$.data.fieldID',
  data_content nvarchar(255) '$.data.content',
  -- ...
  subject_fieldID bigint '$.subject.fieldID',
  subject_content nvarchar(255) '$.subject.content',
)

instead of ... fill other elements.