SELECT * FROM OPENJSON -- Not getting expected values

Question

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),

)

*" I am running in SQL 2019"* Why have you tagged SQL Server 2008 then? What version are you *really* using? `OPENJSON` isn't support in (the completely unsupported for the last 4 years) SQL Server 2008. — Thom A, May 31 '23 at 15:12
None of the elements in your object `factors` are called `ISSUERID`, `ISSUER_NAME`, or `CREDIT_RTG `, those are the values of the `name` element. What are you expected results here exactly? (Images of data isn't helpful). — Thom A, May 31 '23 at 15:23
While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question as text, no images. — Yitzhak Khabinsky, May 31 '23 at 15:26

Charlieface · Answer 1 · 2023-05-31T16:29:19.840

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

Thanks Charlieface, I tried the code above in dbfiddle.uk and it works well with SQL 2019 and newer. It doesn't work with SQL 2016. I will try to troubleshoot but appreciate if you can share SQL 2016 codes. — Overflow, May 31 '23 at 16:23

score 1 · Answer 2 · answered May 31 '23 at 23:25

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.

SELECT * FROM OPENJSON -- Not getting expected values

2 Answers2