Issue Extracting 2nd Level Node Data from JSON File

Question

Post reply

Issue Extracting 2nd Level Node Data from JSON File

Exacto

Valued Member

Points: 63

March 9, 2023 at 4:25 pm

Go to Answer

#4158897

Hi all,

I'm venturing into the world of JSON and am currently trying to extract data out of and into SQL.

Here's the problem, I cannot seem to get data out of the 2nd node in the example below.

There is a related_resources and resource_type node that sit under the notes node. It will not pull out the information, can anyone see what's wrong?

Declare @JSON Nvarchar(max) = N'{"notes" : {"id":9631671,"content":"Here we have some Notes","creator_id":53013,"creator_name":"Test Creator","created_at":"2018-11-09T12:48:34+00:00","updated_at":"2018-11-09T12:48:34+00:00","tags":"","versions":[],"external_system_id":"","visibility_type":"everyone","visibility_user_ids":null,"visibility_user_names":null,"restricted_visibility_group_id":null,"related_resources":[{"id":8635374,"resource_type":"Contact"}]}'

SELECT * 
FROM OPENJSON (@JSON,'$.notes') 
With (id varchar(20)
   ,content NVARCHAR(MAX)
   ,creator_id BIGINT
   ,creator_name VARCHAR(200)
   ,created_at VARCHAR(200)
   ,updated_at VARCHAR(200)
   ,related_resources BIGINT '$.related_resources.id'
   ,resource_type NVARCHAR(MAX) '$.related_resources.resource_type'
   ) as Dataset
Go

This is the output.

Any help is appreciated.

Thanks.

drew.allen

SSC Guru

Points: 76985

March 9, 2023 at 7:00 pm

Answer

#4158982

related_resources is an array of JSON and you haven't supplied the index for the array. The array is zero-based. See the example below.

Declare @JSON Nvarchar(max) = N'{"notes" : {"id":9631671,"content":"Here we have some Notes","creator_id":53013,"creator_name":"Test Creator","created_at":"2018-11-09T12:48:34+00:00","updated_at":"2018-11-09T12:48:34+00:00","tags":"","versions":[],"external_system_id":"","visibility_type":"everyone","visibility_user_ids":null,"visibility_user_names":null,"restricted_visibility_group_id":null,"related_resources":[{"id":8635374,"resource_type":"Contact"}]}'

SELECT * 
FROM OPENJSON (@JSON,'$.notes') 
With (id varchar(20)
   ,content NVARCHAR(MAX)
   ,creator_id BIGINT
   ,creator_name VARCHAR(200)
   ,created_at VARCHAR(200)
   ,updated_at VARCHAR(200)
   ,related_resources BIGINT '$.related_resources[0].id'
   ,resource_type NVARCHAR(MAX) '$.related_resources[0].resource_type'
   ) as Dataset
Go

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Exacto Valued Member Points: 63 More actions · Answer 1

What a superstar! That makes perfect sense now and has worked. Go DREW! Thanks so much!