EN VI

Arrays - OPENJSON TSQL SQL query Nested Json?

How to Arrays - OPENJSON TSQL SQL query Nested Json

Here is my json

declare @response NVARCHAR(MAX) = 
N'
[
  {
    "request": "MSFT",
    "type": "Symbol",
    "results": [
      {
        "type": "Company",
        "id": "0C000008EC",
        "tables": {
          "corporate_calendars": [
            {
              "company_id": "0C000008EC",
              "begin_date_time": "2013-01-24",
              "end_date_time": "2013-01-24",
              "event_type": 8,
              "estimated_date_for_next_event": "1970-01-01",
              "event": "Microsoft Fiscal Year 2013 Second Quarter Earnings",
              "event_fiscal_year": 2013,
              "event_status": "Confirmed",
              "time_zone": "1970-01-01"
            },
            {
              "company_id": "0C000008EC",
              "begin_date_time": "2015-01-26",
              "end_date_time": "2015-01-26",
              "event_type": 13,
              "estimated_date_for_next_event": "1970-01-01",
              "event": "Microsoft Corp Second quarter earnings Conference Call in 2015",
              "event_fiscal_year": 2015,
              "event_status": "Confirmed",
              "time_zone": "1969-12-31"
            }
          ]
        }
      }
    ]
  }
]'

I'm looking for data like this to inset into a table enter image description here

request type    results_type    results_id  company_id  begin_date_time end_date_time   event_type  estimated_date_for_next_event   event   event_fiscal_year   event_status    time_zone
MSFT    Symbol  Company 0C000008EC  0C000008EC  1/24/2013   1/24/2013   8   1/1/1970    Microsoft Fiscal Year 2013 Second Quarter Earnings  2013    Confirmed   1/1/1970
MSFT    Symbol  Company 0C000008EC  0C000008EC  1/26/2015   1/26/2015   13  1/1/1970    Microsoft Corp Second quarter earnings Conference Call in 2015  2015    Confirmed   12/31/1969

this is as far as i got :

select * from openjson(@response)
with(
    [request] nvarchar(100)'$.request',
    [type] nvarchar(100)'$.type'
) x

which gives me the first 2 columns only :

I'm just not good at this :( -- any help is greatly appreciated. TY

Solution:

You can retrieve a whole JSON object or array in the OPENJSON WITH schema, by using nvarchar(max) AS JSON.

Then you can CROSS APPLY OPENJSON feeding that JSON into the next call, and so on.

Do note that this will give you a giant join of all those JSON objects. And you don't need to pass a path explicitly unless it differs from the default.

SELECT
  j1.request,
  j1.type,
  j2.results_type,
  j2.results_id,
  j3.company_id,
  j3.begin_date_time,
  j3.end_date_time,
  j3.event_type,
  j3.estimated_date_for_next_event,
  j3.event,
  j3.event_fiscal_year,
  j3.event_status,
  j3.time_zone
FROM OPENJSON(@response)
  WITH (
    request nvarchar(100),
    type nvarchar(100),
    results nvarchar(max) AS JSON
) j1
CROSS APPLY OPENJSON(j1.results)
  WITH (
    results_type nvarchar(100) '$.type',
    results_id nvarchar(100) '$.id',
    calendars nvarchar(max) '$.tables.corporate_calendars' AS JSON
  ) j2
CROSS APPLY OPENJSON(j2.calendars)
  WITH (
    company_id nvarchar(100),
    begin_date_time date,
    end_date_time date,
    event_type int,
    estimated_date_for_next_event date,
    event nvarchar(1000),
    event_fiscal_year int,
    event_status nvarchar(100),
    time_zone date
  ) j3;

db<>fiddle