CODEWITHSUNDEEP
jsont-sqljson-queryjson-value
kayeesp
kayeesp

Reputation: 55

Read Json Value from a SQL Server table

I have a Json value stored in SQL server table as ntext:

JSON (column: json_val):

[{"prime":{"image":{"id":"123","logo":"","productId":"4000","enable":true},"accountid":"78","productId":"16","parentProductId":"","aprx":"4.599"}}]

select JSON_VALUE(cast(json_val as varchar(8000)), '$.prime.aprx') as px
from table_1
where id = 1

Whenever I execute it, i receive a null. What's wrong with the query?

Thanks for your help!

Upvotes: 1

Views: 6030

Answers (2)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131247

The JSON string is an array with a single item. You need to specify the array index to retrieve a specific item, eg :

declare @t table (json_val  nvarchar(4000))

insert into @t
values ('[{"prime":{"image":{"id":"123","logo":"","productId":"4000","enable":true},"accountid":"78","productId":"16","parentProductId":"","aprx":"4.599"}}]')

select JSON_VALUE(cast(json_val as varchar(8000)), '$[0].prime.aprx') as px
from @t

This returns 4.599

If you want to search all array entries, you'll have to use OPENJSON. If you need to do that though ...

Avoid JSON if possible

JSON storage is not an alternative to using a proper table design though. JSON fields can't be indexed, so filtering by a specific field will always result in a full table scan. Given how regular this JSON string is, you should consider using proper tables instead

Upvotes: 1

Thom A
Thom A

Reputation: 95564

As Panagiotis said in the comments:

As for the JSON path, this JSON string is an array with a single element

Instead, therefore, you can use OPENJSON which would inspect each array:

DECLARE @JSON nvarchar(MAX) = N'[{"prime":{"image":{"id":"123","logo":"","productId":"4000","enable":true},"accountid":"78","productId":"16","parentProductId":"","aprx":"4.599"}}]';

SELECT aprx
FROM (VALUES(@JSON))V(json_val)
     CROSS APPLY OPENJSON(V.json_val)
                 WITH (aprx decimal(4,3) '$.prime.aprx');

As also mentioned, your JSON should already be a string data type (should be/probably an nvarchar(MAX)) so there's no reason to CAST it.

Upvotes: 1

Related Questions