Querying multiple Jsonb columns in postgresql

Question

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%';

I get a syntax error: postgresql error: column reference "value" is ambiguous LINE 3: where value->>'name' like '%Ber%' — B.H.
– B.H., Commented Apr 16, 2020 at 12:24
Your 2nd query has multiple syntax errors before it could ever get to an ambiguous reference. — jjanes
– jjanes, Commented Apr 16, 2020 at 12:59

jjanes · Accepted Answer · 2020-04-16 13:02:09Z

1

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

answered Apr 16, 2020 at 13:02

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Querying multiple Jsonb columns in postgresql

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related