0

SELECT all fields from OPENJSON

I have Json data below. How can I select all available fields from OpenJson? I am using SQL Server 2016.

DECLARE @JsonData varchar(max)

SET @JsonData = 
'{"status":"OK","code":200,"trace_id":"fdfdsgdgfsgdfs","timestamp":"2023-09-12T17:24:20Z","messages": [],"result": {
  "response_metadata" : {
    "total_number_of_instruments" : 2,
    "total_number_of_unresolved_identifiers" : 0,
    "total_number_of_batches" : 1,
    "current_batch" : 1,
    "data_request_id" : "0-gfsgsd17:24:20+0000",
    "data_request_id_expiration_time" : "2023-09-13T17:24:20+0000",
    "resolvedfactors" : [ "ESG_RATING", "ISSUER_NAME", "ISSUER_ISIN", "ISSUER_TICKER", "ISSUERID" ]
  },
  "data" : [ {
    "requested_id" : "IID000000003053597",
    "issuer_metadata" : [ {
      "ISSUERID" : "IID000000003053597",
      "ISSUER_NAME" : "Crowdstrike Holdings Inc",
      "ISSUER_ISIN" : "US22788C1053",
      "as_of_date" : "2019-09-01",
      "valid_until_date" : "2019-12-01"
    } ],
    "factors" : [ {
      "name" : "ISSUER_NAME",
      "data_values" : [ {
        "value" : "Crowdstrike Holdings Inc",
        "as_of_date" : "2019-09-30",
        "as_at_date" : "2023-09-12T17:24:20.921118Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "Crowdstrike Holdings Inc",
        "as_of_date" : "2019-10-31",
        "as_at_date" : "2023-09-12T17:24:20.921118Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "Crowdstrike Holdings Inc",
        "as_of_date" : "2019-11-29",
        "as_at_date" : "2023-09-12T17:24:20.921118Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      } ]
    }, {
      "name" : "ISSUER_ISIN",
      "data_values" : [ {
        "value" : "US22788C1053",
        "as_of_date" : "2019-09-30",
        "as_at_date" : "2023-09-12T17:24:20.921091Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "US22788C1053",
        "as_of_date" : "2019-10-31",
        "as_at_date" : "2023-09-12T17:24:20.921091Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "US22788C1053",
        "as_of_date" : "2019-11-29",
        "as_at_date" : "2023-09-12T17:24:20.921091Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      } ]
    }, {
      "name" : "ISSUERID",
      "data_values" : [ {
        "value" : "IID000000003053597",
        "as_of_date" : "2019-09-30",
        "as_at_date" : "2023-09-12T17:24:00Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "IID000000003053597",
        "as_of_date" : "2019-10-31",
        "as_at_date" : "2023-09-12T17:24:00Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "IID000000003053597",
        "as_of_date" : "2019-11-29",
        "as_at_date" : "2023-09-12T17:24:00Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      } ]
    } ]
  }, {
    "requested_id" : "IID000000002633749",
    "issuer_metadata" : [ {
      "ISSUERID" : "IID000000002633749",
      "ISSUER_NAME" : "CRESTWOOD MIDSTREAM PARTNERS LP",
      "ISSUER_ISIN" : "US226373AL21",
      "ISSUER_TICKER" : "CEQP",
      "as_of_date" : "2019-09-01",
      "valid_until_date" : "2019-12-01"
    } ],
    "factors" : [ {
      "name" : "ESG_RATING",
      "data_values" : [ {
        "value" : "B",
        "as_of_date" : "2019-09-30",
        "as_at_date" : "2023-09-12T17:24:20.925397Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "B",
        "as_of_date" : "2019-10-31",
        "as_at_date" : "2023-09-12T17:24:20.925397Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "B",
        "as_of_date" : "2019-11-29",
        "as_at_date" : "2023-09-12T17:24:20.925397Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      } ]
    }, {
      "name" : "ISSUER_NAME",
      "data_values" : [ {
        "value" : "CRESTWOOD MIDSTREAM PARTNERS LP",
        "as_of_date" : "2019-09-30",
        "as_at_date" : "2023-09-12T17:24:20.925444Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "CRESTWOOD MIDSTREAM PARTNERS LP",
        "as_of_date" : "2019-10-31",
        "as_at_date" : "2023-09-12T17:24:20.925444Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "CRESTWOOD MIDSTREAM PARTNERS LP",
        "as_of_date" : "2019-11-29",
        "as_at_date" : "2023-09-12T17:24:20.925444Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      } ]
    }, {
      "name" : "ISSUER_ISIN",
      "data_values" : [ {
        "value" : "US226373AL21",
        "as_of_date" : "2019-09-30",
        "as_at_date" : "2023-09-12T17:24:20.925421Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "US226373AL21",
        "as_of_date" : "2019-10-31",
        "as_at_date" : "2023-09-12T17:24:20.925421Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "US226373AL21",
        "as_of_date" : "2019-11-29",
        "as_at_date" : "2023-09-12T17:24:20.925421Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      } ]
    }, {
      "name" : "ISSUER_TICKER",
      "data_values" : [ {
        "value" : "CEQP",
        "as_of_date" : "2019-09-30",
        "as_at_date" : "2023-09-12T17:24:20.925464Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "CEQP",
        "as_of_date" : "2019-10-31",
        "as_at_date" : "2023-09-12T17:24:20.925464Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "CEQP",
        "as_of_date" : "2019-11-29",
        "as_at_date" : "2023-09-12T17:24:20.925464Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      } ]
    }, {
      "name" : "ISSUERID",
      "data_values" : [ {
        "value" : "IID000000002633749",
        "as_of_date" : "2019-09-30",
        "as_at_date" : "2023-09-12T17:24:00Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "IID000000002633749",
        "as_of_date" : "2019-10-31",
        "as_at_date" : "2023-09-12T17:24:00Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      }, {
        "value" : "IID000000002633749",
        "as_of_date" : "2019-11-29",
        "as_at_date" : "2023-09-12T17:24:00Z",
        "reference_issuer_id" : null,
        "reference_issuer_name" : null
      } ]
    } ]
  } ]
}}'

I tried the following query, but I am not getting any values. Is there a way to select all the available data? TIA

SELECT
    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') = 'as_of_date' 
                 THEN json_value(y.value, '$.as_of_date') END) AS as_of_date, 
    MAX(CASE WHEN json_value(x.value, '$.name') = 'valid_until_date' 
                 THEN json_value(y.value, '$.as_at_date') END) AS valid_until_date
FROM
    OPENJSON(@JsonData, '$.result.data.factors') x
CROSS APPLY
    OPENJSON(x.value,'$.data_values') y
--cross apply openjson(x.as_of_date,'$.data_values') z
GROUP BY
    y.[key]

Here is the result

ISSUERID as_of_date valid_until_date
Improve this question
1
  • Could you please add your expected output ?
    – Sumant Kumar Mandal
    Commented Sep 12, 2023 at 18:02

1 Answer 1

Reset to default
1

You can flatten your JSON data combining OPENJSON and conditional aggregation

select t.data_idx, t.factors_idx, t.factor_name, d.[key] val_idx, 
     max(case e.[key] when 'value' then e.value end) [value],
     max(case e.[key] when 'as_of_date' then e.value end) as_of_date,
     max(case e.[key] when 'as_at_date' then e.value end) as_at_date,
     max(case e.[key] when 'reference_issuer_id' then e.value end) reference_issuer_id,
     max(case e.[key] when 'reference_issuer_name' then e.value end) reference_issuer_name
from (
     select a.[key] data_idx,  b.[key] factors_idx, 
       max(case c.[key] when 'name' then c.value end) factor_name,
       max(case c.[key] when 'data_values' then c.value end) factor_values 
     from openjson(@JsonData, '$.result.data') a
     cross apply openjson(a.value, '$.factors') b
     cross apply openjson(b.value, '$') c
     group by a.[key],  b.[key]
    ) t
cross apply openjson(t.factor_values,'$') d
cross apply openjson(d.[value], '$') e
group by t.data_idx, t.factors_idx, t.factor_name, d.[key];

db<>fiddle

The data can then be processed according to requirements.

Improve this answer
2
  • Hi Serg, Thanks. Is there a way to select the result in horizontal format? For example, The result will show column headers ISSUERID, as_of_date, valid_until_date and the values underneath?
    – Overflow
    Commented Sep 12, 2023 at 20:13
  • Please edit your question and add your expected output for sample data.
    – Serg
    Commented Sep 13, 2023 at 6:54

Not the answer you're looking for? Browse other questions tagged or ask your own question.