sqlpostgresqlleft-joinjsonbset-returning-functions

How to join on a nested value from a jsonb column?

I have a PostgreSQL 11 database with these tables:

CREATE TABLE stats (
   id integer NOT NULL,
   uid integer NOT NULL,
   date date NOT NULL,
   data jsonb DEFAULT '[]'::json NOT NULL
);
INSERT INTO stats(id, uid, date, data) VALUES
   (1, 1, '2020-10-01', '{"somerandomhash":{"source":"thesource"}}');

CREATE TABLE links(
   id integer NOT NULL,
   uuid uuid NOT NULL,
   path text NOT NULL
);
INSERT INTO links(id, uuid, path) VALUES
   (1, 'acbd18db-4cc2-f85c-edef-654fccc4a4d8', 'thesource');

My goal is to create a new table reports with data from the stats table, but with a new key from the links table. It will look like this:

CREATE TABLE reports(
    id integer NOT NULL,
    uid integer NOT NULL,
    date date NOT NULL,
    data jsonb DEFAULT '[]'::json NOT NULL
);

INSERT INTO reports(id, uid, date, data) VALUES
   (1, 1, 2020-10-01, {"uuid":{"source":"thesource"});

To this end, I tried to left join the table links in order to retrieve the uuid column value - without luck:

SELECT s.uid, s.date, s.data->jsonb_object_keys(data)->>'source' as path, s.data->jsonb_object_keys(data) as data, l.uuid
FROM stats s LEFT JOIN links l ON s.data->jsonb_object_keys(data)->>'source' = l.path

I tried to use the result of s.data->jsonb_object_keys(data)->>'source' in the left join, but got the error:

ERROR:  set-returning functions are not allowed in JOIN conditions

I tried using LATERAL but still not valid result.
How to make this work?

Solution

  • jsonb_object_keys() is a set-returning function which cannot be used the way you do - as the error messages tells you. What's more, json_object_keys() returns top-level key(s), but it seems you are only interested in the value. Try jsonb_each() instead:

    SELECT s.id
     , s.uid
     , s.date
     , jsonb_build_object(l.uuid::text, o.value) AS new_data
FROM   stats s
CROSS  JOIN LATERAL jsonb_each(s.data) o  -- defaults to column names (key, value)
LEFT   JOIN links l ON l.path = o.value->>'source';

    fiddle

    jsonb_each() returns top-level key and value. Proceed using only the value.

    The nested JSON object seems to have the constant key name 'source'. So the join condition is l.path = o.value->>'source'.

    Finally, build the new jsonb value with jsonb_build_object().

    While this works as demonstrated, a couple of questions remain: