Read Json Value from a SQL Server table

Question

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!

Why `cast(json_val as varchar(8000))` ? Isn't that field a text field already? As for the JSON path, this JSON string is an *array* with a single element. — Panagiotis Kanavos, Dec 22 '20 at 10:02
Why are you storing such regular data in a JSON field though? — Panagiotis Kanavos, Dec 22 '20 at 10:13

score 1 · Answer 1 · answered Dec 22 '20 at 10:07

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.

score 1 · Accepted Answer · answered Dec 22 '20 at 10:07

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

Read Json Value from a SQL Server table

2 Answers2