Postgres 11 join tables using jsonb values

Question

I have two tables:

create table device_group_v2
(
    id        uuid not null primary key,
    name      varchar(255),
    filter    jsonb
);

with one entry:

+--------------------------------------+------------+-------------------------------------+
|                  id                  |    name    |               filter                |
+--------------------------------------+------------+-------------------------------------+
| 92f9ac68-7baa-4322-8eb1-e6b9e83dd2ef | Devices-UK | [{"key": "country", "value": "uk"}] |
+--------------------------------------+------------+-------------------------------------+

and

create table device_entity
(
    id       uuid not null primary key,
    name     varchar(255),
    metadata jsonb
);

With one entry:

+--------------------------------------+---------------------+-------------------+
|                  id                  |        name         |     metadata      |
+--------------------------------------+---------------------+-------------------+
| 75cf91d0-01b7-42e0-9791-86def0dc4b7b | Device 7 - Metadata | {"country": "uk"} |
+--------------------------------------+---------------------+-------------------+

Is it possible to join both tables, so I get all devices having key/value as defined in the filter in TestGroup?

In the given example it's this query, but I need to fill 'country' and 'uk' dynamically from the "filter" column in the device_group_v2 table.

SELECT * from device_entity where (metadata ->> 'country') = 'uk';

If there are multiple key-value entries, all should be combined with an OR. I need to fetch any of these elements that match the given filters.

Charlieface · Accepted Answer · 2022-09-18 04:09:42Z

There are a number of different ways to do this, but the easiest might be:

Join the tables on the condition that NOT EXISTS anything in a subquery that:
You break-out the filter array using jsonb_to_recordset
Then EXCEPT from it the break-out of the metadata object using jsonb_each_text

SELECT e.*
FROM device_group_v2 g
JOIN device_entity e
  ON NOT EXISTS (
    SELECT f."key", f.value
    FROM jsonb_to_recordset(g.filter) AS f("key" text, value text)

    EXCEPT

    SELECT md."key", md.value
    FROM jsonb_each_text(e.metadata) md
    )
WHERE g.name = 'Devices-UK';

db<>fiddle

This gets you the result if you want to AND all the filters. If you want to OR them, change the NOT EXISTS to EXISTS and EXCEPT to INTERSECT

SELECT e.*
FROM device_group_v2 g
JOIN device_entity e
  ON EXISTS (
    SELECT f."key", f.value
    FROM jsonb_to_recordset(g.filter) AS f("key" text, value text)

    INTERSECT

    SELECT md."key", md.value
    FROM jsonb_each_text(e.metadata) md
    )
WHERE g.name = 'Devices-UK';

db<>fiddle

Stack Exchange Network

Postgres 11 join tables using jsonb values

1 Answer 1

Your Answer

Hot Network Questions

Postgres 11 join tables using jsonb values

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions