How to Search SQL Column Containing JSON Array

How to search SQL column containing JSON array

For doing a search in a JSON array, one needs to use OPENJSON

DECLARE @table TABLE (Col NVARCHAR(MAX))
INSERT INTO @table VALUES ('{"names":["Joe","Fred","Sue"]}')

SELECT * FROM @table 
WHERE 'Joe' IN ( SELECT value FROM OPENJSON(Col,'$.names'))  

or as an alternative, one can use it with CROSS APPLY.

SELECT * FROM 
    @table 
    CROSS APPLY OPENJSON(Col,'$.names')
WHERE value ='Joe'

SQL find elements is in JSON array column

You need an OPENJSON() call to parse the stored JSON array. The result is a table with columns key, value and type and in the value column is each element from the parsed JSON array. The column data type is nvarchar(max) with the same collation as tags column collation.

SELECT *
FROM (VALUES
   (1, '["test01"]'),
   (2, '["test02","test03"]')
) table_tags (id, tags)
WHERE EXISTS (
   SELECT 1 FROM OPENJSON(tags) WHERE [value] IN ('test01', 'test02')
)

Search SQL JSON array of objects

You can use OPENJSON to extract the id values from your JSON objects and CROSS APPLY that to your logs table, selecting only rows that have an id value in the JSON object of 13:

SELECT logs.*
FROM logs
CROSS APPLY OPENJSON([log], '$.tags') WITH (id INT '$.id')
WHERE id = 13

Demo on dbfiddle

Querying Json array column and other current table column

As I mentioned in the comments, use OPENJSON against the column, not a scalar variable which contains the value of just one of your rows, and none of the other row data.

SELECT P.NameID,
       ONs.[Name],
       ONs.[DateTime],
       P.CurrentName
FROM dbo.Persons P
     CROSS APPLY OPENJSON(P.OtherNames)
                 WITH ([Name] varchar(255),
                       [DateTime] date) ONs;

Note that as your value [DateTime] is culture dependant, you may need to define it as a varchar(10) in the WITH, and then CONVERT it to a date in the SELECT with a style code.

How to get data from json column in SQL Server that starts with array element

SELECT Name FROM dbo.JData 
CROSS APPLY OPENJSON (JsonData)
WITH 
(
  Categories nvarchar(max) AS json,
  Id uniqueidentifier,
  [Name] varchar(10)
);

• Example db<>fiddle

Select rows where json array contains specific element

select *
from   orders
where  json_exists(products, '$[*]?(@ == 8)')
;

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/condition-JSON_EXISTS.html#GUID-D60A7E52-8819-4D33-AEDB-223AB7BDE60A

Get rows whose JSON array contains an object with a value

You can use the contains operator @>

select *
from (your query)
where record @> '{"tags": [{"id": 3}]}'

This assumes that the column record is of type jsonb (which it should be). If it's not, you need to cast it: record::jsonb

---

Related Topics