How to parse JSON in where clause (SQL Server)

Question

I am trying to parse a column

Example Column Data

[{"id":"100190","role":"Company"},{"id":"1049","role":"Person"}]

Example Table

| item | perm                                                             | 
|------|------------------------------------------------------------------|
| a100 | NULL                                                             |
| a101 | NULL                                                             |
| b200 | [{"id":"100190","role":"Company"},{"id":"1049","role":"Person"}] |
| b201 | [{"id":"100190","role":"Company"},{"id":"1049","role":"Person"}] |

Example Procedure

declare @id int = 1049

select *
from someTable
where contains(openjson(perm, '$') with (
  id int '$.id'
), @id)

**Expeced Output **

| item | perm                                                             | 
|------|------------------------------------------------------------------|
| b200 | [{"id":"100190","role":"Company"},{"id":"1049","role":"Person"}] |
| b201 | [{"id":"100190","role":"Company"},{"id":"1049","role":"Person"}] |

How do I select rows that contain a given id from an embedded array of JSON?

critical_error · Accepted Answer · 2019-10-11 17:32:56Z

Using CROSS APPLY on your JSON will get you what you want.

Here's an example you can run in SSMS:

-- Create table --
DECLARE @Data TABLE ( item VARCHAR(10), perm NVARCHAR(MAX) );

-- Add sample data --
INSERT INTO @Data ( item, perm ) VALUES
( 'a100', NULL ),
( 'a101', NULL ),
( 'b200', '[{"id":"100190","role":"Company"},{"id":"1049","role":"Person"}]' ),
( 'b201', '[{"id":"100190","role":"Company"},{"id":"1049","role":"Person"}]' );

-- Create id param --
DECLARE @id INT = 1049;

-- Select rows where JSON.id equals @id --
SELECT
    d.item, d.perm, j.id
FROM @Data AS d
CROSS APPLY (

    SELECT id FROM OPENJSON( d.perm ) WITH (
        id INT '$.id'
    )

) AS j
WHERE
    j.id = @id
ORDER BY
    d.item;

Which returns:

+------+------------------------------------------------------------------+------+
| item |                               perm                               |  id  |
+------+------------------------------------------------------------------+------+
| b200 | [{"id":"100190","role":"Company"},{"id":"1049","role":"Person"}] | 1049 |
| b201 | [{"id":"100190","role":"Company"},{"id":"1049","role":"Person"}] | 1049 |
+------+------------------------------------------------------------------+------+

Lukasz Szozda · Accepted Answer · 2019-10-11 17:26:46Z

2

You could use OPENJSON to parse JSON array and CROSS APPLY to filter rows:

declare @id int = 1049;

select t.*
from t
cross apply(select * from OPENJSON(t.perm, '$') WITH (id INT '$.id') where id = @id) sub;

db<>fiddle demo

answered Oct 11, 2019 at 17:26

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Collectives™ on Stack Overflow

How to parse JSON in where clause (SQL Server)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related