Using OpenJson in select statement [duplicate]

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

@VLOOKUP why would you care about the version of ssms? Surely you only care about the version of SQL server? — Dale K
– Dale K, Commented May 22, 2024 at 6:28
@VLOOKUP there are already enough people confused by the difference between ssms and SQL server without making it worse. — Dale K
– Dale K, Commented May 22, 2024 at 19:12

marc_s · Accepted Answer · 2024-05-22 05:40:19Z

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

answered May 22, 2024 at 5:40

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Over a year ago

I went with this answer thanks for the reply and the help

Dale K · Accepted Answer · 2024-05-22 05:38:02Z

1

SELECT 
    part.value AS partNumber,
    t.Price
FROM 
    tableName t
CROSS APPLY 
    OPENJSON(t.Details) 
    WITH (partNumber nvarchar(255) '$.partNumber') AS part
WHERE 
    t.Price = 10.00;

edited May 22, 2024 at 5:38

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered May 22, 2024 at 3:14

Edward Thomas

1221 silver badge7 bronze badges

2 Comments

Dale K Over a year ago

A good answer contains an explanation in addition to a working query.

John Over a year ago

thanks for taking the time to respond

Collectives™ on Stack Overflow

Using OpenJson in select statement [duplicate]

2 Answers 2

1 Comment

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Linked

Related