SQL JSON PATH How to access a json array by index after extracting from a larger json set

Question

Probably a poor title. apologies. I'm working with a large set of json data with nested arrays that are often without a name. I'm trying to extract part of this data and then just access it by array offset.

Example that returns the array data as rows

DECLARE @json NVARCHAR(MAX)
SET @json =  
N'[  
       { "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 },  
       { "id" : 5,"info": { "name": "Jane", "surname": "Smith", "skills": [["SQL", "C#", "Azure"],["ABC","DEF","BLASH"]] }, "dob": "2005-11-04T12:00:00" }  
 ]'  

SELECT  arr 
FROM OPENJSON(@json)  
  WITH (id int 'strict $.id',  
        firstName nvarchar(50) '$.info.name', lastName nvarchar(50) '$.info.surname',  
        age int, dateOfBirth datetime2 '$.dob',
    skillarr nvarchar(max) '$.info.skills' as json) 

    cross apply openjson( skillarr ) with ( arr nvarchar(MAX) '$' AS JSON )

this will return ["SQL", "C#", "Azure"] ["ABC","DEF","BLASH"]

but I would then like to return only the first elements of each array subset. i.e "SQL" "ABC"

I have tried a second cross apply but cannot figure out the path convention

cross apply openjson (arr ) with (firstskill nvarchar(10) '$.[0]')

is not valid. Anyone know how I just access by index on unnamed data?

Thanks

Hi i think this post and example can help you : stackoverflow.com/a/54233113/9940803 — Sanpas
– Sanpas, Commented Jan 22, 2019 at 22:18
Thanks. The issue for me seems to be that my array data does not have a name : value structure, but just value. In the example you pointed out all the elements have the full name : value paring and are accessed as such from the json path. In the linked example I could do $.attributes[0]' and access the first array element by name, In my case I dont have the name part. — Himilou
– Himilou, Commented Jan 22, 2019 at 22:27

Sanpas · Accepted Answer · 2019-01-23 10:01:13Z

1

Hi i think this query can respond :

DECLARE @json NVARCHAR(MAX)
SET @json =  
N'[  
       { "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 },  
       { "id" : 5,"info": { "name": "Jane", "surname": "Smith", "skills": [["SQL", "C#", "Azure"],["ABC","DEF","BLASH"]] }, "dob": "2005-11-04T12:00:00" }  
 ]'  

SELECT  /*id,*/ a /*,arr*/
FROM OPENJSON(@json)  
  WITH (id int 'strict $.id',  
        firstName nvarchar(50) '$.info.name', lastName nvarchar(50) '$.info.surname',  
        age int, dateOfBirth datetime2 '$.dob',
    skillarr nvarchar(max) '$.info.skills' as json) 

    cross apply openjson( skillarr ) with ( arr nvarchar(MAX) '$' AS JSON)
    cross apply openjson( arr ) with (a nvarchar(MAX) '$')

Result was :

a
SQL
C#
Azure
ABC
DEF
BLASH

For get the first skil you have to see with row_number or mock id in cross apply i think it's possible way to find what you excepted.

I think this query can be work :

DECLARE @json NVARCHAR(MAX)
SET @json =  
N'[  
       { "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 },  
       { "id" : 5,"info": { "name": "Jane", "surname": "Smith", "skills": [["SQL", "C#", "Azure"],["ABC","DEF","BLASH"]] }, "dob": "2005-11-04T12:00:00" }  
 ]'  

SELECT SUBJSONPARSER.First_Skill 
FROM (
    SELECT  /*id,*/ a as 'First_Skill' /*,arr*/, ROW_NUMBER() OVER(Partition by arr ORDER BY arr) AS 'Row_Number'
    FROM OPENJSON(@json)  
      WITH (id int 'strict $.id',  
            firstName nvarchar(50) '$.info.name', lastName nvarchar(50) '$.info.surname',  
            age int, dateOfBirth datetime2 '$.dob',
        skillarr nvarchar(max) '$.info.skills' as json) 
        cross apply openjson( skillarr ) with ( arr nvarchar(MAX) '$' AS JSON)
        cross apply openjson(arr) with (a nvarchar(MAX) '$') ) SUBJSONPARSER
WHERE SUBJSONPARSER.Row_Number = 1

And result :

First_Skill
ABC
SQL

edited Jan 23, 2019 at 10:01

answered Jan 23, 2019 at 7:42

Sanpas

1,18010 silver badges30 bronze badges

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

2 Comments

Himilou Over a year ago

Thanks Pascal, thats what I was trying to accomplish!!

N8allan Over a year ago

Note that SQL Server now supports '$.sql:identity()' so row number is no longer required. See OpenJson docs.

Collectives™ on Stack Overflow

SQL JSON PATH How to access a json array by index after extracting from a larger json set

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related