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!
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