CODEWITHSUNDEEP
postgresqljsonb
B.H.
B.H.

Reputation: 15

Querying multiple Jsonb columns in postgresql

I am trying to query multiple jsonb columns in postgresb. I can only figure out how to query one specific jsonb column in postegresql. These columns have the same nested structure. Here is an example of the structure. This structure is in two different jsonb columns. 

{
    "6": {
        "start_date": "2008-01-02",
        "end_date": "2008-01-03",
        "name": "Berry",
        "order_number": 6,
    },
    "0": {
        "start_date": "2008-05-05",
        "end_date": "2008-05-06",
        "name": "John",
        "order_number": 0,
    }
}

I can successfully query within one jsonb column using the jsonb_each function.

Example: 

select (value->>'start_date')::timestamp as start_time, (value->>'end_date')::timestamp as end_time, value->>'name' as user, value->>'order_number' as order_num
from table, jsonb_each("jsonb_column_1")
where value->>'name' like '%Ber%';

I want to query for the "names" from multiple columns. Maybe something like this: 

 select c1.(value->>'start_date')::timestamp as start_time_1, c2.(value->>'start_date')::timestamp as start_time_2, c1.(value->>'name' as user1), c2(value->>'name' as user2)
 from table, jsonb_each("jsonb_column_1")c1, jsonb_each("jsonb_column_2")c2
 where value->>'name' like '%Ber%';

Upvotes: 0

Views: 390

Answers (1)

jjanes
jjanes

Reputation: 44137

Combine them with a UNION ALL.

select (value->>'start_date')::timestamp as start_time, (value->>'end_date')::timestamp as end_time, value->>'name' as user, value->>'order_number' as order_num
from table1, jsonb_each("jsonb_column_1")
where value->>'name' like '%Ber%'
UNION ALL
select (value->>'start_date')::timestamp as start_time, (value->>'end_date')::timestamp as end_time, value->>'name' as user, value->>'order_number' as order_num
from table1, jsonb_each("jsonb_column_2")
where value->>'name' like '%Ber%'

Upvotes: 1

Related Questions