I'm trying to parse the 'custinfo' array to rows, rather than specific columns how I have in my query (there can be none or many values in the array)
DECLARE @json NVARCHAR(MAX) ='{
"customer": [
{
"id": "123",
"history": [
{
"id": "a123",
"dates": [
{
"date": "2022-03-19",
"details": {
"custinfo": [
"male",
"married"
],
"age": 40
}}]}]}]}'
SELECT
JSON_VALUE ( j.[value], '$.id' ) AS CustId,
JSON_VALUE ( m.[value], '$.id' ) AS CustId_Hist,
JSON_VALUE ( a1.[value], '$.date' ) AS date,
JSON_VALUE ( a1.[value], '$.details.age' ) AS age,
JSON_VALUE ( a1.[value], '$.details.custinfo[0]' ) AS custinfo0,
JSON_VALUE ( a1.[value], '$.details.custinfo[1]' ) AS custinfo1
FROM OPENJSON( @json, '$."customer"' ) j
CROSS APPLY OPENJSON ( j.[value], '$."history"' ) AS m
CROSS APPLY OPENJSON ( m.[value], '$."dates"' ) AS a1
Desired results:
Like I mentioned in the comments, I would switch to using
WITH
clauses and defining your columns and their data types. You can then also get the values, into 2 separate rows you want, with the following. Note tbhe extraOPENJSON
at the end, which treats thecustinfo
as the array it is; returning 2 rows (1 for each value in the array):