Why is my SQL SELECT returning all NULL values from a valid JSON column?

Question

I have a table in an SQL Server database with columns containing valid json data.

example of data in a field: [{"max": 0, "min": 0, "price": 1000}]

Column datatype is nvarchar(max).

Code:

Select json_value(odometer_surcharges, '$.price') AS price from postgres.warranty_terms

The problem is that it returns NULL values for every record.

Answer 1

since you DIDN NOT proviced any data and/or table structure this is an example how to read json from column:

DECLARE @t TABLE (id INT IDENTITY(1,1) not null, rawjson nvarchar(2000) NULL)
INSERT INTO @t VALUES
('[{"max": 0, "min": 0, "price": 1000}]')
,(null)
,('[{"max": 10, "min": 1, "price": 2.30}]')

SELECT id, [Max], [min], price
FROM @t t
CROSS APPLY OPENJSON(t.rawjson) WITH ([max] int, [min] int, price decimal(8,3))

result:

id	MAX	MIN	price
1	0	0	1000.000
3	10	1	2.300

UPDATE

if you want to use Json_value (treat json as array):

SELECT JSON_VALUE(rawjson, '$[0].price') AS price from @t