Why is not removing properly from the JSON array?

Question

I have a JSON array, I want to give an entry and this entry should be removed from the JSON array.

For example, the array:

SET @os ='[
{
    "id": "66",
    "date_end": "2022-01-09 18:00:00",
    "occupied": "1",
    "date_begin": "2022-01-09 17:30:00",
    "idCategory": "[1,2,3]",
    "date_requested": "2022-01-02 14:59:27"
},
{
    "id": "39",
    "date_end": "2022-01-03 11:30:00",
    "occupied": "1",
    "date_begin": "2022-01-03 08:00:00",
    "idCategory": "3",
    "date_requested": "2022-01-01 22:40:58"
}
]';

And the entry that I want to be removed:

SET @test ='
{
    "id": "39",
    "date_end": "2022-01-03 11:30:00",
    "occupied": "1",
    "date_begin": "2022-01-03 08:00:00",
    "idCategory": "3",
    "date_requested": "2022-01-01 22:40:58"
}';

My code is something like:

SET @new_os = (select json_arrayagg(js) from json_table(@os, '$[*]' columns ( js json path '$')) as jt 
    where json_extract(js, '$.date_end') <> json_extract(@test, '$.date_end') and json_extract(js, '$.id') <> json_extract(@test, '$.id') 
    and json_extract(js, '$.date_begin') <> json_extract(@test, '$.date_begin') and json_extract(js, '$.date_requested') <> json_extract(@test, '$.date_requested') 
    and json_extract(js, '$.idCategory') <> json_extract(@test, '$.idCategory') and json_extract(js, '$.occupied') <> json_extract(@test, '$.occupied'));

SELECT @os,@new_os;

But in the end I get that @new_os is NULL, and it should be:

[
{
    "id": "66",
    "date_end": "2022-01-09 18:00:00",
    "occupied": "1",
    "date_begin": "2022-01-09 17:30:00",
    "idCategory": "[1,2,3]",
    "date_requested": "2022-01-02 14:59:27"
}]

LukStorms · Accepted Answer · 2022-01-26 11:54:21Z

Instead of filtering where the json columns don't match,
it could be filtering out where the columns do match.

SET @new_os = (
select json_pretty(json_arrayagg(os.js)) as new_os
from json_table(@os, '$[*]' columns (js json path '$')) as os
where not exists (
  select 1
  from json_table(@test, '$' columns ( js json path '$')) as tst
  where json_extract(os.js, '$.id') = json_extract(tst.js, '$.id')
    and json_extract(os.js, '$.date_end') = json_extract(tst.js, '$.date_end')
    and json_extract(os.js, '$.date_begin') = json_extract(tst.js, '$.date_begin')
    and json_extract(os.js, '$.date_requested') = json_extract(tst.js, '$.date_requested')
    and json_extract(os.js, '$.idCategory') = json_extract(tst.js, '$.idCategory')
    and json_extract(os.js, '$.occupied') = json_extract(tst.js, '$.occupied')
));

SELECT @os, @new_os;

Demo on db<>fiddle here

Collectives™ on Stack Overflow

Why is not removing properly from the JSON array?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related