OPENJSON TSQL SQL query Nested Json

Question

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

Charlieface · Accepted Answer · 2024-03-10 21:19:05Z

2

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

answered Mar 10, 2024 at 21:19

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user254060 Over a year ago

that is sweet - i will look into cross apply - its complicated to understand since i am not a developer but looks very powerful. TY so much i truly appreciate it.

user254060 Over a year ago

So i started working with Cross Appy and its super powerful. TY for that. Here is another one : dbfiddle.uk/6JqXYebh I cant seem to get to the ownership summary node ? 'ownership_summary' i.e.

Charlieface Over a year ago

Needs to be nvarchar(max) AS JSON and the path needs to start $.. You might want to use OUTER instead of CROSS, but note that you are currently getting a giant cartesian join between all the tables dbfiddle.uk/EU_na848

user254060 Over a year ago

awesome and TY sir : I just noticed i didn't say TY - so sorry - its people like you that make coding fun and enjoyable.

user254060 Over a year ago

hi @charlieface : I have another one for you - dbfiddle.uk/5besOGoU I cant get the tables to work for the Balance_Sheet. Please can you help sir. Its probably something silly that i am missing !

|

Collectives™ on Stack Overflow

OPENJSON TSQL SQL query Nested Json

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related