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
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
