Why is array_remove (PostgreSQL) not working in this case?

Question

This is not working correctly: SELECT array_remove(array_agg(s1->>'karten'),'8 eich.jpg') from spiele;

The output: ["3 eich.jpg","8 eich.jpg","5 sche.jpg","2 herz.jpg","1 laub.jpg","4 eich.jpg","2 sche.jpg","5 laub.jpg","4 herz.jpg","4 sche.jpg"]

The datatype of s1 is json; s1->>'karten' is an array

Interesting... While the horse has no name, it seems that he does have a country :-) — David דודו Markovitz
– David דודו Markovitz, Commented Aug 18, 2022 at 7:46

user330315 · Accepted Answer · 2022-08-18 07:33:16Z

2

If karten refers to a JSON array in the, then s1 ->> 'karten doesn't return each element individually, but a one string representing the array. So array_agg() doesn't really aggregate multiple values - only one. The result is an array with a single element - that happens to look like a JSON array.

You can remove an element from a JSON array if the values is jsonb (the recommended data type to handle JSON in Postgres anyway) using the - operator:

select (s1 -> 'karten')::jsonb - '8 eich.jpg'

will return a jsonb value that is an without the key '8 eich.jpg'.

Unfortunately there is no easy conversion from a JSON array to a native array. Search this site, there are multiple answers for that.

answered Aug 18, 2022 at 7:33

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why is array_remove (PostgreSQL) not working in this case?

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