How to search SQL column containing JSON array

by

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"

More Related Contents:

  1. SQL where clause odd behavior [closed]
  2. to select all the employees who have joined most recently under king
  3. How do I escape a single quote in SQL Server?
  4. Simple way to transpose columns and rows in SQL?
  5. Count work days between two dates
  6. Only inserting a row if it’s not already there
  7. Unpivot with column name
  8. Selecting data from two different servers in SQL Server
  9. Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [duplicate]
  10. SQL JOIN vs IN performance?
  11. Most efficient way in SQL Server to get date from date+time?
  12. Convert varchar into datetime in SQL Server
  13. TSQL Pivot without aggregate function
  14. How to generate a range of dates in SQL Server
  15. SQL RANK() versus ROW_NUMBER()
  16. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  17. Check if a row exists, otherwise insert
  18. SQL : BETWEEN vs =
  19. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  20. How do I perform a GROUP BY on an aliased column in SQL Server?
  21. How do I make a composite key with SQL Server Management Studio?
  22. How to get N rows starting from row M from sorted table in T-SQL
  23. How can I generate a temporary table filled with dates in SQL Server 2000?
  24. How can I group time by hour or by 10 minutes?
  25. Is SQL or even TSQL Turing Complete?
  26. Getting Dates between a range of dates
  27. SQL Server SELECT INTO @variable?
  28. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  29. Select a column if other column is null
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment