Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Remove values from JSON array based on values in another table

I need to remove some values in a JSON array if they match with values from another table, if match id and pax:

Here is some data

declare @T1 Table (id int ,pax int ,rooms nvarchar(512))
declare @T2 Table (id int ,pax int ,rooms nvarchar(512))

insert into @T1 (id,pax,rooms)
VALUES (1,3,'["I23","I31","I1","I5","I15","B2","I14","I4","I11","I3","I15","I5","I15","I15","I31","I31","I21","I35","B5-B3","I5-I15","I13","I6-I5-I4-I3","B6","I36-I26-I16-I6"]'),
(1,4,'["I1-I11","I21-I31","I24-I34","B3-B5","B4-B6","I25-I35","I4-I5","I6-I16","I26-I36","I15-I25","I14-I24","I14-I4","I23-I31","I15-I5","I6-I16","I35-I25","I16-I6","I26-I36","B3-B5","B1-B3","I36-I26","I23-I13","B3","I3-I4"]'),
(2,5,'["I4-I5","I1-I11","I21-I31","B4-B6","I15-I25","I6-I16","I26-I36","I31-I23","I23-I13","I11-I21","I35-I25","B5-B3","I14-I4","I24-I34","B3-B1","I5-I15","B2-B4","I24-I14","I5-I3"]')

insert into @T2 (id,pax,rooms)
VALUES (1,3,'["I24-I34-I25-I35-I26-I36"]'), -- not exists
(1,3,'["I36-I26-I16-I6"]'),
(1,3,'["I6-I5-I4-I3"]'),
(1,3,'["I6-I16"]'), --not exists
(1,3,'["B6"]'),
(1,3,'["I13"]'),
(1,4,'["B1-B3"]'),
(1,4,'["B3"]'),
(1,4,'["I3-I4"]'),
(2,5,'["I5-I3"]')

So I need to remove from @T1 with id = 1 and pax=3 the values "I24-I34-I25-I35-I26-I36","I36-I26-I16-I6","I6-I5-I4-I3","I6-I16","B6","I13" (if exists)

Therefore the expected value will be

["I23","I31","I1","I5","I15","B2","I14","I4","I11","I3","I15","I5","I15","I15","I31","I31","I21","I35","B5-B3","I5-I15"]

I tried to use the few JSON functions available in SQL Server but clearly my skills are not enough to find the right solution

Here is my attempt:

SELECT  t1.rooms,
        FilteredArray = JSON_QUERY(CONCAT('[', STRING_AGG(QUOTENAME(oj.Value,'"'), ','), ']'))
FROM    @T1 AS t1
        CROSS APPLY 
        (   SELECT  oj.Value
            FROM    OPENJSON(t1.rooms) AS oj
            join @T2 as t2 on t2.id=t1.id and t2.pax=t1.pax
            where t1.rooms<>t2.rooms
        ) AS oj
        group by t1.rooms

But instead of removing values it concatenate 3 times the @T1 value
Can someone suggest the path to follow to achieve the target?

Tags:

2

Answers


  1. 0

    Please try the following solution based on the set based operator EXCEPT.

    SQL

    DECLARE @T1 TABLE (id INT ,pax INT ,rooms NVARCHAR(512));
DECLARE @T2 TABLE (id INT ,pax INT ,rooms NVARCHAR(512));

INSERT INTO @T1 (id,pax,rooms)
VALUES (1,3,'["I23","I31","I1","I5","I15","B2","I14","I4","I11","I3","I15","I5","I15","I15","I31","I31","I21","I35","B5-B3","I5-I15","I13","I6-I5-I4-I3","B6","I36-I26-I16-I6"]'),
(1,4,'["I1-I11","I21-I31","I24-I34","B3-B5","B4-B6","I25-I35","I4-I5","I6-I16","I26-I36","I15-I25","I14-I24","I14-I4","I23-I31","I15-I5","I6-I16","I35-I25","I16-I6","I26-I36","B3-B5","B1-B3","I36-I26","I23-I13","B3","I3-I4"]'),
(2,5,'["I4-I5","I1-I11","I21-I31","B4-B6","I15-I25","I6-I16","I26-I36","I31-I23","I23-I13","I11-I21","I35-I25","B5-B3","I14-I4","I24-I34","B3-B1","I5-I15","B2-B4","I24-I14","I5-I3"]');

INSERT INTO @T2 (id,pax,rooms)
VALUES (1,3,'["I24-I34-I25-I35-I26-I36"]'), -- not exists
(1,3,'["I36-I26-I16-I6"]'),
(1,3,'["I6-I5-I4-I3"]'),
(1,3,'["I6-I16"]'), --not exists
(1,3,'["B6"]'),
(1,3,'["I13"]'),
(1,4,'["B1-B3"]'),
(1,4,'["B3"]'),
(1,4,'["I3-I4"]'),
(2,5,'["I5-I3"]');

WITH rs AS
(
    SELECT t1.id, t1.pax, oj.Value
    FROM  @T1 AS t1
    CROSS APPLY  OPENJSON(t1.rooms) AS oj
    EXCEPT
    SELECT t2.id, t2.pax, TRIM('["]' FROM t2.rooms)
    FROM @T2 AS t2
)
SELECT rs.id, rs.pax
    , FilteredArray = JSON_QUERY(CONCAT('[', STRING_AGG(QUOTENAME(rs.Value,'"'), ','), ']'))
FROM rs
GROUP BY rs.id, rs.pax;

    Output

    id pax FilteredArray
    1 3 ["B2","B5-B3","I1","I11","I14","I15","I21","I23","I3","I31","I35","I4","I5","I5-I15"]
    1 4 ["B3-B5","B4-B6","I14-I24","I14-I4","I15-I25","I15-I5","I16-I6","I1-I11","I21-I31","I23-I13","I23-I31","I24-I34","I25-I35","I26-I36","I35-I25","I36-I26","I4-I5","I6-I16"]
    2 5 ["B2-B4","B3-B1","B4-B6","B5-B3","I11-I21","I14-I4","I15-I25","I1-I11","I21-I31","I23-I13","I24-I14","I24-I34","I26-I36","I31-I23","I35-I25","I4-I5","I5-I15","I6-I16"]
    Login or Signup to reply.
  2. 0

    Using Yitzhak’s data, perhaps something like:

    SELECT id, pax
    , FilteredArray = JSON_QUERY(CONCAT('[', STRING_AGG(QUOTENAME(oj.Value,'"'), ',') WITHIN GROUP(ORDER BY CAST([key] AS INT)), ']'))
FROM @T1 AS t1
CROSS APPLY  OPENJSON(t1.rooms) AS oj
WHERE NOT EXISTS(
    SELECT  1
    FROM    @T2 AS t2
    WHERE   TRIM('["]' FROM t2.rooms) = oj.Value
    AND t2.id = t1.id
    AND t2.pax = t1.pax
)
GROUP BY id, pax;

    Outputs:

    id pax FilteredArray
    1 3 ["I23","I31","I1","I5","I15","B2","I14","I4","I11","I3","I15","I5","I15","I15","I31","I31","I21","I35","B5-B3","I5-I15"]
    1 4 ["I1-I11","I21-I31","I24-I34","B3-B5","B4-B6","I25-I35","I4-I5","I6-I16","I26-I36","I15-I25","I14-I24","I14-I4","I23-I31","I15-I5","I6-I16","I35-I25","I16-I6","I26-I36","B3-B5","I36-I26","I23-I13"]
    2 5 ["I4-I5","I1-I11","I21-I31","B4-B6","I15-I25","I6-I16","I26-I36","I31-I23","I23-I13","I11-I21","I35-I25","B5-B3","I14-I4","I24-I34","B3-B1","I5-I15","B2-B4","I24-I14"]

    EXISTS allows more flexibility to handle things that should or should not exist. I also added the ORDER BY for the STRING_AGG to get correct sequence

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search