Unable to extract property with SQL JSON

I have the folowing Json data stored in a sql table :

{"OrderNumber":"12450-OF","OrderType":"OF"}

I need to extract the OrderNumber from a sql query

The folowing statement returns null:

select 
   JSON_QUERY(Metadata,'$.OrderNumber') AS 'orderNumber'
from Documents

where Documents is my table, and metadata is the column where my json data is stored.

CodePudding user response：

You need to use JSON_VALUE() to extract a scalar value from a JSON content. JSON_QUERY() is usually used to extract an object or an array from a JSON string.

SELECT JSON_VALUE(Metadata,'$.OrderNumber') AS 'orderNumber'
FROM (VALUES
   ('{"OrderNumber":"12450-OF","OrderType":"OF"}')
) Documents (Metadata)

Note, that if you want to extract more values from the stored JSON, OPENJSON() with explicit schema is another option:

SELECT *
FROM Documents d
CROSS APPLY OPENJSON(d.Metadata, '$') WITH (
   OrderNumber varchar(10) '$.OrderNumber',
   OrderType varchar(2) '$.OrderType'
) j

CodePudding user response：

You just need to replace function Json_Query to JSON_VALUE.

JSON_VALUE use for getting value from json.
JSON_Query use for getting object from json string.

For example, if you have:

"OrderNumber":["12450-OF","12450-02"]

then your query will return object

["12450-OF","12450-02"]