CODEWITHSUNDEEP
sqlsql-server
Pokemon Pro
Pokemon Pro

Reputation: 21

Select data from JSON

Currently I am stuck at how to select a value from JSON.

Table Inventory:

Name   | Json_Column
-------+--------------------------------------------------------------------
Ball   | [{"skuId":"1","quantity":1,"skuName":"$5 valley ball"},{"skuId":"2","quantity":1,"skuName":"$10 BasketBall"}]
Racket | [{"skuId":"3","quantity":1,"skuName":"$5 Badminton racket"}]

I want to convert the following result using select SQL statement to:

Name   | Json_Column
-------+---------------------------------------
Ball   | $5 valley ball, $10 BasketBall
Racket | $5 Badminton racket

When I use this SQL statement to select, it returns null; I need some help for the SQL.

select JSON_VALUE (Json_Column, '$.skuName') as Name 
from Inventory

Upvotes: 1

Views: 3732

Answers (1)

Manoz
Manoz

Reputation: 6617

Try with OPENJSON

SELECT * FROM OPENJSON(Json_Column, '$') 
WITH (skuId int'$.skuId',quantity int '$.quantity',skuName varchar(50) '$.skuName')

Update

Okay, so to select from the table you can do cross apply with OPENJSON

SELECT skuName from Inventory
CROSS APPLY
OPENJSON (Json_Column)
WITH (skuId int'$.skuId',quantity int '$.quantity',skuName varchar(50) '$.skuName')

Upvotes: 1

Related Questions