I have several columns in my database. In one of these columns is a JSON string like: fyvfvv.
[
{
"Value": "0000138010687",
"Name": "Code"
}
]
I need to select those rows from the database, whose JSON is filled with the value of the mask "0000****", or the value is greater than a certain number.
Can you tell me how to do it in the best way?
You need to parse the stored JSON with OPENJSON() and use the appropriate WHERE clause.
Data:
SELECT *
INTO JsonTable
FROM (VALUES
('[{"Value": "00001380", "Name": "Code"}]'),
('[{"Value": "0000138010688", "Name": "Code"}]'),
('[{"Value": "0000138010689", "Name": "Code"}]')
) v (JsonColumn)
Statement (to get the rows):
SELECT *
FROM JsonTable t
WHERE EXISTS (
SELECT 1
FROM OPENJSON(t.JsonColumn) WITH (
[Value] varchar(100) '$.Value',
[Name] varchar(100) '$.Name'
)
WHERE [Value] LIKE '0000____'
)
Statement (to get the rows and the parsed JSON):
SELECT *
FROM JsonTable t
CROSS APPLY OPENJSON(t.JsonColumn) WITH (
[Value] varchar(100) '$.Value',
[Name] varchar(100) '$.Name'
) j
WHERE j.[Value] LIKE '0000____'
Both statements return the correct results, even if the parsed JSON array contains more than one item.
Assuming the json array contains one element only, then we can use JSON_VALUE() to get the element value by path :
select *
from (
select *,
JSON_VALUE(JsonColumn, '$[0].Value') as theValue,
JSON_VALUE(JsonColumn, '$[0].Name') as theName
from JsonTable
) as s
where theName = 'Code' and theValue like '0000%';
OPENJSON? What have you tried? Why didn't it work."Code":"0000138010687"?{"key":"value"}you'll be able to useJSON_VALUE(field,'$.Code')to get the code value. You'll also be able to create a computed and persisted field that can be indexed egThatCode AS JSON_VALUE(field,'$.Code') PERSISTED. Your query could become as simple asWHERE ThatCode LIKE '0000%'and take advantage of indexes.the value is greater than a certain numberthat's another unusual requirement. Strings aren't numbers either in JSON or SQL. In JSON,2is a number but"2"is a string that's greater than"0000138010687"because strings are ordered alphabetically, and"2"comes after"0". You can useTRY_CASTto try and parse the value into a number.TRY_CASTwill returnNULLif the cast fails. If the JSON string had a normal schema you could useThatCode_AsNumber AS TRY_CAST(JSON_VALUE(field,'$.Code') as number(18,0)) PERSISTED