sqlarraysjsonpostgresqljsonb

Distinct for jsonb_path_query_array


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?


Solution

  • 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

    View on DB Fiddle

    #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

    View on DB Fiddle