simple JSON array to Rows

Question

I would like to take the following JSON and convert it to an array

declare @com nvarchar(MAX)

set @com = '{"IDs":[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,83,84,88,89,90,91,97,98,99,100,101,102,104,108,109,110,111,112,114,115,116,118,119,121,122,123,124,125,126,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,152,153,154]}';

select isJson(@com)

select * from openjson(@com)
    with (commodities varchar(50) 'strict $.IDs')

But when I do I get the following error what am I doing wrong ?

Object or array cannot be found in the specified JSON path.

Your syntax with (commodities varchar(50) 'strict $.IDs') requires the array index be supplied such as with (commodities varchar(50) 'strict $.IDs[0]'). However, -Shnugo has answered correctly for this I believe in order to extract all values in the array. — Dave C
– Dave C, Commented Dec 12, 2018 at 17:49

Gottfried Lesigang · Accepted Answer · 2018-12-12 17:46:22Z

3

You've put the path in the wrong place. Try this

select * from openjson(@com,'strict $.IDs')

The returned set includes the element's position (in key) and its value and type.

answered Dec 12, 2018 at 17:46

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

Comments

yg-dba · Accepted Answer · 2021-02-12 18:31:22Z

In case anyone is looking to join to fields that are in a JSON array in MySQL, here is a query that uses FIND_IN_SET to join an array of IDs to another table.

It does not perform well but it works.

(MySQL 5.7 does not support REGEXP_REPLACE)

select c.classId, c.className, replace(replace(replace(replace(sc.classes->>'$.classIds','"',''),'[',''),']',''),' ','')
from studentClasses sc
join classes c on find_in_set(c.classId,replace(replace(replace(replace(sc.classes->>'$.classIds','"',''),'[',''),']',''),' ',''))
where 1
limit 10;

The classes JSON column looks like this:

    "classIds": [
        "d4ae08d0-c27c-11ea-87f0-3508520d9867",
        "556c3060-d0f5-11ea-995d-9709c7e03f55",
        "558eac80-d0f5-11ea-995d-9709c7e03f55",
        "559192b0-d0f5-11ea-995d-9709c7e03f55",
        "d57613c0-c27c-11ea-87f0-3508520d9867",
        "d551e9f0-c27c-11ea-87f0-3508520d9867",
        "d4b44a60-c27c-11ea-87f0-3508520d9867"
    ]
}

Collectives™ on Stack Overflow

simple JSON array to Rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related