0

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.

Improve this question
1

1 Answer 1

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Apologies. I had more detail but got chided for having too much information. For future reference what more information would be better. Table with a column nvarchar(max) with this as a sample record content: [{"max": 0, "min": 0, "price": 1000}] I thought this code sample would indicate fields and table. Select json_value(odometer_surcharges, '$.price') AS price from postgres.warranty_terms where postgres.warranty_terms is the table name and the column name is odometer_surcharges. The data I wish to extract is 'max', 'min' and 'price'
I am also uncertain why my SELECT statement only returns null values.
Are you referring to datatype? I noted it in the primary post as nvarchar(max).
JSON_VALUE(rawjson, '$[0].price') - will work, because there is an array

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.