Как искать столбец SQL, содержащий массив JSON

Question

У меня есть столбец SQL, который имеет один массив JSON:

{"names":["Joe","Fred","Sue"]}

Учитывая строку поиска, как я могу использовать SQL для поиска совпадения в массиве имен? Я использую SQL 2016 и посмотрел на JSON_QUERY, но не знаю, как искать совпадения в массиве JSON. Что-то вроде ниже было бы хорошо.

SELECT *
FROM table
WHERE JSON_QUERY(column, '$.names') = 'Joe'

Answer 1

Для поиска в массиве JSON необходимо использовать 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'))  

или в качестве альтернативы, вы можете использовать его с CROSS APPLY,

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

Answer 2

Синтаксис Postgres

Когда вы знаете ключ, содержащий данные:

SELECT column_name from table_name where column_name->>'key' LIKE '%QUERYSTRING%';

Если вы не знаете ключ, содержащий данные:

SELECT column_name from table_name where column_name::text LIKE '%QUERYSTRING%';

Answer 3

Это очень просто, легко можно сделать с помощью функции JSON_CONTAINS().

SELECT * FROM  table
where  JSON_CONTAINS(column, 'joe','$.name');

Answer 4

Просто хочу добавить к существующим ответам простое решение, как проверить, содержит ли массив внутри json значение:

      DECLARE @Json NVARCHAR(max) = '{"names":["Joe","Fred","Sue"]}'
IF EXISTS (SELECT value FROM OPENJSON(@Json,'$.names') WHERE value = 'Joe')
    PRINT 'Array contains "Joe"'
ELSE
    PRINT 'Array does not contain "Joe"'

Answer 5

Вы можете найти совпадение в массиве JSON с помощью следующего запроса:

      SELECT JSON_EXTRACT(COLUMN, "$.names") AS NAME
FROM TABLE JSON_EXTRACT(COLUMN, "$.names") != ""

Замените TABLE на имя вашей таблицы и COLUMN на имя столбца в таблице. ключ, который я упомянул, имя, как оно было в вашем вопросе.