CODEWITHSUNDEEP
sqlpostgresql
Suraj
Suraj

Reputation: 2477

Combining and removing duplicates in jsonb - PostgreSQL

I've managed to combine jsonb arrays using ||, but I'm not able to remove duplicates and 'N/A' from the jsonb array.

Current code :

SELECT
jsonb_path_query_array(column1, '$.key') ||
jsonb_path_query_array(column2, '$.key') ||
jsonb_path_query_array(column3, '$.key')
FROM
table;

Current output : (jsonb)

['N/A', 'N/A', 'N/A']
['N/A', 'AGENT', 'N/A']
['N/A', 'AGENT', 'AGENT']
['SYSTEM', 'N/A', 'N/A']

Desired output

NULL
AGENT
AGENT
SYSTEM

Upvotes: 1

Views: 1025

Answers (1)

Ajax1234
Ajax1234

Reputation: 71451

You can use a cte with array_to_json:

with vals(a) as (
  select jsonb_path_query_array(column1, '$.key') || jsonb_path_query_array(column2, '$.key') || jsonb_path_query_array(column3, '$.key') from data
),
r_na(a) as (
    select (select array_to_json(array_agg(distinct v.value)) 
            from jsonb_array_elements(v1.a) v where v.value::text <> '"N/A"')  
    from vals v1
)
select case when a::text = 'null' then null else a -> 0 end r from r_na;

Output:

null
AGENT
AGENT
SYSTEM

Upvotes: 1

Related Questions