Postgres 13 join from another table on JSONB array of String

Question

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

lemon · Accepted Answer · 2023-01-22 18:06:24Z

2

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.

answered Jan 22, 2023 at 18:06

lemon

15.6k6 gold badges23 silver badges42 bronze badges

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

Collectives™ on Stack Overflow

Postgres 13 join from another table on JSONB array of String

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related