I would like to look up a property in another table B, where the source is part of a comma-separated string inside a JSONB column of table A.

create table option
(
    optionid bigint not null primary key,
    attributevalues jsonb default '{}'::jsonb
);

create table district
(
    districtid bigint not null primary key,
    uid varchar(11) not null,
    name varchar(230) not null unique
);

INSERT into option values (1, '{"value": "N8UXIAycxy3,uVwyu3R4nZG,fuja8k8PCFO,y0eUmlYp7ey", "attribute": {"id": "K54wAf6EX0s"}}'::jsonb);

INSERT INTO district (districtid, uid, name) VALUES
(1, 'N8UXIAycxy3', 'district1'),
(2, 'uVwyu3R4nZG', 'district2'),
(3, 'fuja8k8PCFO', 'district3'),
(4, 'y0eUmlYp7ey', 'district4');

I can get all the items split by , but how do I "join" to look up the name (e.g. N8UXIAycxy3 --> district1)?

I tried to "join" in a traditional sense but this will not work as the district_uid is not accessible for the query as such:

SELECT UNNEST(STRING_TO_ARRAY(co.attributevalues #>> '{"K54wAf6EX0s", "value"}', ',')) AS district_uid
FROM option o
JOIN district d on district_uid = d.uid;

I would like to have the query result: district1,district2,district3,district4. Is this possible or do I need a loop?

DB Fiddle

CodePudding user response：

You need to convert to array the comma separated string, i.e. attributevalues->>'value':

select name
from option
cross join unnest(string_to_array(attributevalues->>'value', ',')) as district_uid
join district on uid = district_uid

DB fiddle.