Have some trouble with jsonb_path_query_array
in pgsql.
Is there a way to distinct values inside arrays?
I have something like this
select jsonb_path_query_array(public.table.column, '$.Idents.type' ) from public.table;
And get output like this:
[1] [Type1, Type1, Type1, Type2, Type2, Type2]
[2] [Type3, Type3, Type3, Type4, Type4]
Is there a way to get something like this?
[1] [Type1, Type2]
[2] [Type3, Type4]
So, basically i need a distinct thing but inside processing array.
Is there a way to do this with some arg i didn't think about?
directly you can not use DISTINCT, but a Combination from jsonb_path_query and array_agg would do the trick
Still you would need a unique column to do my approach
Schema (PostgreSQL v14)
create table public.example(id int,test jsonb);
insert into public.example (id, test) values (1, '{"Idents": [{"id": "id0", "type": "Type1"}, {"id": "id1", "type": "Type1"}, {"id": "id2", "type": "Type2"}, {"id": "id3", "type": "Type2"}], "val0": 1725397140, "val1": -10800}');
insert into public.example (id,test) values (2,'{"Idents": [{"id": "id4", "type": "Type3"}, {"id": "id5", "type": "Type3"}, {"id": "id6", "type": "Type4"}, {"id": "id6", "type": "Type4"}], "val0": 1725397140, "val1": -10800}');
Query #1
WITH CTE AS (select DISTINCT id ,jsonb_path_query(public.example.test, '$.Idents.type' ) val
from public.example)
SELECT id, array_agg(val)
FROM CTE
GROUP BY id
ORDER BY id;
id | array_agg |
---|---|
1 | Type1,Type2 |
2 | Type4,Type3 |
#A better solution would be to use jsonb_agg instead, like described in the comment
Schema (PostgreSQL v14)
create table public.example(id int,test jsonb);
insert into public.example (id, test) values (1, '{"Idents": [{"id": "id0", "type": "Type1"}, {"id": "id1", "type": "Type1"}, {"id": "id2", "type": "Type2"}, {"id": "id3", "type": "Type2"}], "val0": 1725397140, "val1": -10800}');
insert into public.example (id,test) values (2,'{"Idents": [{"id": "id4", "type": "Type3"}, {"id": "id5", "type": "Type3"}, {"id": "id6", "type": "Type4"}, {"id": "id6", "type": "Type4"}], "val0": 1725397140, "val1": -10800}');
Query #1
WITH CTE AS (select DISTINCT id ,jsonb_path_query(public.example.test, '$.Idents.type' ) val
from public.example)
SELECT id, jsonb_agg(val)
FROM CTE
GROUP BY id
ORDER BY id;
id | jsonb_agg |
---|---|
1 | Type1,Type2 |
2 | Type4,Type3 |