Using OpenJson in select statement

Question

If I have a database table with (details being a JSON string):

Id   Details                            Price
----------------------------------------------
1   {"partNumber":"012345","qty":14}    14.99
2   {"partNumber":"22222","qty":11}      4.88
3   {"partNumber":"44444","qty":1}       3.24
4   {"partNumber":"72431223","qty":9}    3.33
5   {"partNumber":"98989","qty":1}       3.33

I only want to return partNumber and price.

So for example

SELECT
    Details.partNumber, Price 
FROM
    tableName 
WHERE
    price < 10.00

I've been playing about with this and so far have

SELECT
    Details.partNumber, price 
FROM
    tableName (SELECT partNumber  
               FROM OPENJSON(tableName.Details)) 
WHERE
    Price = 10.00

which doesn't work. Any help would be great. Thanks for any replies

Answer 1

You can use the JSON_VALUE function to extract a single piece of data from a JSON structure in a column.

Change your code to look like this:

SELECT
    JSON_VALUE(Details, '$.partNumber') AS 'PartNumber', 
    price 
FROM
    tableName 
WHERE
    Price < 10.00