JSON to SQL Server: Convert sub-array of values to column

Question

I have JSON like

{  
    "url": {
        "web_categories": [{ "id": 226 } , { "id": 401 }]
  },  
   "account":"Basic"  
}

I would like to output in SQL a column that contains the IDs of all web_categories a URL was matched to.

| webcat |
|--------|
| 226    |
| 401    |

Using SQL Server 2016 / Azure SQL DB's support for JSON, how would one achieve that?

Reproducible example

DECLARE @jsonInfo VARCHAR(MAX)  
SET @jsonInfo =N'{  
    "url": {
        "web_categories": [{ "id": 226 }]
  },  
   "account":"Basic"  
}'

Previous iterations

select * from openjson(@jsonInfo)  with (webcat varchar(12) '$.url.web_categories.id[0]')
select * from openjson(@jsonInfo)  with (webcat varchar(12) '$.url.web_categories.id')
select * from openjson(@jsonInfo)  with (webcat varchar(12) '$.url.web_categories')
select * from openjson(@jsonInfo,'$.url.web_categories.id[0]')
select * from openjson(@jsonInfo,'$.url.web_categories.id')
select * from openjson(@jsonInfo,'$.url.web_categories')
select JSON_QUERY(@jsonInfo, '$.url.web_categories.id[0]') webcat
select JSON_QUERY(@jsonInfo, '$.url.web_categories.id')    webcat
select JSON_QUERY(@jsonInfo, '$.url.web_categories')       webcat
select JSON_VALUE(@jsonInfo, '$.url.web_categories.id[0]') webcat
select JSON_VALUE(@jsonInfo, '$.url.web_categories.id')    webcat
select JSON_VALUE(@jsonInfo, '$.url.web_categories')       webcat

Are you going to mark your answer as accepted?

Jovan MSFT
– Jovan MSFT

2016-09-12 21:21:49 +00:00
Commented Sep 12, 2016 at 21:21 — Jovan MSFT
– Jovan MSFT, Commented Sep 12, 2016 at 21:21
Marked as accepted

Steph Locke
– Steph Locke

2016-09-13 08:01:11 +00:00
Commented Sep 13, 2016 at 8:01 — Steph Locke
– Steph Locke, Commented Sep 13, 2016 at 8:01

Steph Locke · Accepted Answer · 2016-09-07 19:20:12Z

2

Using the default schema, records are returned with a value column that contains JSON for each item in the array. This JSON then needs to be processed.

DECLARE @jsonInfo VARCHAR(MAX)  
SET @jsonInfo =N'{  
    "url": {
        "web_categories": [{ "id": 226 },{ "id": 411 }]
  },  
   "account":101  
}'  


 SELECT JSON_VALUE(value,'$.id') AS ids FROM OPENJSON(@jsoninfo,'$.url.web_categories')

answered Sep 7, 2016 at 19:20

Steph Locke

6,1964 gold badges44 silver badges79 bronze badges

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

1 Comment

Jovan MSFT Over a year ago

In this case, explicit schema mightt be better choice because it will directly cast ids to int: select id from openjson(@jsonInfo,'$.url.web_categories') with (id int)

Collectives™ on Stack Overflow

JSON to SQL Server: Convert sub-array of values to column

Reproducible example

Previous iterations

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Reproducible example

Previous iterations

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related