Postgres 13 join from another table on JSONB array of String-CodePudding

I have a table with JSONB column. In this column we store identifiers of another table as json array of strings. How can I join the tables

Table Customer:

CustomerID	Name	Campaigns (JSONB)
1	John	[ "rxuatoak", "vsnxcvdsl", "jkiasokd" ]
2	Mick	[ "jdywmsks", "nxbsvwios", "jkiasokd" ]

Table Campaign:

CampaignID	Identifier	CampaignName
1	rxuatoak	Alpha
2	vsnxcvdsl	Bravo
3	jkiasokd	Charlie
4	jdywmsks	Delta
5	nxbsvwios	Echo

Result something like:

CustomerID	Name	CampaignNames
1	John	Alpha, Bravo, Charlie
2	Mick	Delta, Echo, Charlie

I tried many ways, and could only find online help with json objects inside the jsonb column. My jsonb column has simple array of strings.

Using POSTGRES 13

CodePudding user response：

You can apply a JOIN operation between the two tables on condition that an identifier is found within a campaign (using ? operator). Then apply aggregation with STRING_AGG, with respect to the "CustomerID" and "Name"

SELECT customer.CustomerID,     
       customer.Name_,
       STRING_AGG(campaign.CampaignName, ',') AS CampaignNames
FROM       customer
INNER JOIN campaign
        ON customer.Campaigns ? campaign.Identifier
GROUP BY customer.CustomerID,   
         customer.Name_

Check the demo here.