SELECT * FROM OPENJSON -- Not getting expected values

overflow
May 31, 2023
158 views
0 votes
2 Answers

I am getting 0 row when selecting from OPENJSON below. I am expecting to get all 3 values "AA" for different as_of_date for CREDIT_RTG . I tried different select statements. I am running in SQL 2016.
Expected result should be similar to this

enter image description here

Codes are posted below. Thanks in advance

DECLARE @jsonVal varchar(max)
SET @jsonVal = '{
  "status": "OK",
  "code": 200,
  "trace_id": "3eea64f2a7917c11",
  "timestamp": "2023-05-31T14:36:02Z",
  "messages": [],
  "result": {
    "response_metadata": {
      "total_number_of_instruments": 1,
      "data_request_id_expiration_time": "2023-06-01T14:36:02+0000",
      "resolvedfactors": [
        "CREDIT_RTG",
        "ISSUER_NAME",
        "ISSUER_ISIN",
        "ISSUER_SEDOL",
        "ISSUERID"
      ]
    },
    "data": [
      {
        "requested_id": "IID000000002745031",
        "issuer_metadata": [
          {
            "ISSUERID": "IID000000002745031",
            "ISSUER_NAME": "ALPHABET INC.",
            "ISSUER_ISIN": "US02079K3059",
            "ISSUER_TICKER": "GOOGL",
            "as_of_date": "2019-09-01",
            "valid_until_date": "2019-09-14"
          },
          {
            "ISSUERID": "IID000000002745031",
            "ISSUER_NAME": "ALPHABET INC.",
            "ISSUER_ISIN": "US02079K1079",
            "ISSUER_TICKER": "GOOGL",
            "as_of_date": "2019-09-14",
            "valid_until_date": "2019-12-01"
          }
        ],
        "factors": [
          {
            "name": "CREDIT_RTG",
            "data_values": [
              {
                "value": "AA",
                "as_of_date": "2019-09-30",
                "as_at_date": "2023-05-31T14:36:02.245957Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              },
              {
                "value": "AA",
                "as_of_date": "2019-10-31",
                "as_at_date": "2023-05-31T14:36:02.245957Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              },
              {
                "value": "AA",
                "as_of_date": "2019-11-29",
                "as_at_date": "2023-05-31T14:36:02.245957Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              }
            ]
          },
          {
            "name": "ISSUER_NAME",
            "data_values": [
              {
                "value": "ALPHABET INC.",
                "as_of_date": "2019-09-30",
                "as_at_date": "2023-05-31T14:36:02.246042Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              },
              {
                "value": "ALPHABET INC.",
                "as_of_date": "2019-10-31",
                "as_at_date": "2023-05-31T14:36:02.246042Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              },
              {
                "value": "ALPHABET INC.",
                "as_of_date": "2019-11-29",
                "as_at_date": "2023-05-31T14:36:02.246042Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              }
            ]
          },
          {
            "name": "ISSUER_ISIN",
            "data_values": [
              {
                "value": "US02079K1079",
                "as_of_date": "2019-09-30",
                "as_at_date": "2023-05-31T14:36:02.246006Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              },
              {
                "value": "US02079K1079",
                "as_of_date": "2019-10-31",
                "as_at_date": "2023-05-31T14:36:02.246006Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              },
              {
                "value": "US02079K1079",
                "as_of_date": "2019-11-29",
                "as_at_date": "2023-05-31T14:36:02.246006Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              }
            ]
          },
          {
            "name": "ISSUER_SEDOL",
            "data_values": [
              {
                "value": "BYY88Y7",
                "as_of_date": "2019-09-30",
                "as_at_date": "2023-05-31T14:36:02.246084Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              },
              {
                "value": "BYY88Y7",
                "as_of_date": "2019-10-31",
                "as_at_date": "2023-05-31T14:36:02.246084Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              },
              {
                "value": "BYY88Y7",
                "as_of_date": "2019-11-29",
                "as_at_date": "2023-05-31T14:36:02.246084Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              }
            ]
          },
          {
            "name": "ISSUERID",
            "data_values": [
              {
                "value": "IID000000002745031",
                "as_of_date": "2019-09-30",
                "as_at_date": "2023-05-31T14:36:00Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              },
              {
                "value": "IID000000002745031",
                "as_of_date": "2019-10-31",
                "as_at_date": "2023-05-31T14:36:00Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              },
              {
                "value": "IID000000002745031",
                "as_of_date": "2019-11-29",
                "as_at_date": "2023-05-31T14:36:00Z",
                "reference_issuer_id": null,
                "reference_issuer_name": null
              }
            ]
          }
        ]
      }
    ]
  }
}'


SELECT ISSUERID, CREDIT_RTG,  as_of_date  
FROM OPENJSON(@jsonVal,'$.result.data.factors')
with
(
    [ISSUERID] [varchar](20),
    [ISSUER_NAME] [varchar](50) ,
    CREDIT_RTG [varchar](20),
    as_of_date [varchar](20),
    value [varchar](20),

)

Answers

The way you are parsing the JSON appears to be unusual. It seems the data_values arrays are correlated across different factors objects.

So you need to first parse out the factors array into separate JSON sub-arrays, and pivot it up into columns. Then you can split out the ISSUERID array first, and correlate back to the others using JSON_VALUE. To get the array index for each row, you first need a separate call to OPENJSON with no schema, which returns key, value where the key is the index into the array.

Note that because both $.result.data and factors are arrays, you need separate OPENJSON calls to split them out. If there is only ever one data object then you can combine them with just OPENJSON(@jsonval, '$.result.data[0].factors').

SELECT
  final.*
FROM OPENJSON(@jsonVal,'$.result.data')
  WITH
  (
    factors nvarchar(max) AS JSON
  ) j1
CROSS APPLY (
    SELECT pvt.*
    FROM OPENJSON(j1.factors)
      WITH
      (
        name varchar(50),
        data_values nvarchar(max) AS JSON
      ) j2
    PIVOT (MAX(data_values) FOR name IN
      (
        ISSUERID, ISSUER_NAME, CREDIT_RTG
      )
    ) pvt
) pvt
CROSS APPLY (
    SELECT
      [ISSUERID] = issuerid.value,
      [ISSUER_NAME] = JSON_VALUE(pvt.ISSUER_NAME, '$[' + array.[key] + '].value'),
      CREDIT_RTG    = JSON_VALUE(pvt.CREDIT_RTG,  '$[' + array.[key] + '].value'),
      issuerid.as_of_date
    FROM OPENJSON(pvt.ISSUERID) array
    CROSS APPLY OPENJSON(array.value)
      WITH
      (
        value varchar(50),
        as_of_date date
      ) issuerid
) final;

db<>fiddle

SQL Server 2016 does not allow dynamic JSON paths, so instead you need to do a JOIN between all the different arrays based on key

-- etc
CROSS APPLY (
    SELECT
      [ISSUERID] = issuerid.value,
      [ISSUER_NAME] = JSON_VALUE(arrISSUER_NAME.value, '$.value'),
      CREDIT_RTG    = JSON_VALUE(arrCREDIT_RTG.value,  '$.value'),
      issuerid.as_of_date
    FROM OPENJSON(pvt.ISSUERID) arrISSUERID
    JOIN OPENJSON(pvt.ISSUER_NAME) arrISSUER_NAME ON arrISSUER_NAME.[key] = arrISSUERID.[key]
    JOIN OPENJSON(pvt.CREDIT_RTG ) arrCREDIT_RTG  ON arrCREDIT_RTG.[key]  = arrISSUERID.[key]
    CROSS APPLY OPENJSON(arrISSUERID.value)
      WITH
      (
        value varchar(50),
        as_of_date date
      ) issuerid
) final

db<>fiddle

Here’s a bit simpler, albeit likely less performant openjson version:

select max(case when json_value(x.value, '$.name') = 'ISSUER_ISIN' then json_value(y.value, '$.value') end) AS ISSUER_ISIN
  , max(case when json_value(x.value, '$.name') = 'ISSUER_SEDOL' then json_value(y.value, '$.value') end) AS ISSUER_SEDOL
  , max(case when json_value(x.value, '$.name') = 'ISSUERID' then json_value(y.value, '$.value') end) AS ISSUERID
  , max(case when json_value(x.value, '$.name') = 'CREDIT_RTG' then json_value(y.value, '$.value') end) AS CREDIT_RTG
  , max(case when json_value(x.value, '$.name') = 'CREDIT_RTG' then json_value(y.value, '$.as_of_date') end) AS as_of_date
from openjson(@jsonVal, '$.result.data[0].factors') x
cross apply openjson(x.value,'$.data_values') y
group by y.[key]

It’s a group by that fetches all the factors arrays and then pivots the fields depending on which array it operates on by using the array index as grouping field.

It’s easy to add other fields by changing the json_values.

Please signup or login to give your own answer.

Click here to cancel reply.

SELECT * FROM OPENJSON — Not getting expected values

Answers