CODEWITHSUNDEEP
sqlpostgresqljsonb
user72840184
user72840184

Reputation: 43

How do I do a left outer join on an array?

I am having trouble doing a left outer join with a jsonb array involved.

My schema: 

CREATE TABLE IF NOT EXISTS cusips (
  name TEXT,
  cusip TEXT,
  ticker TEXT
);

CREATE TABLE IF NOT EXISTS companies (
  name TEXT,
  data JSONB
);

My query:

INSERT INTO cusips (name, cusip, ticker) VALUES
  ('Berkshire Official', '90210', 'BRKA'),
  ('Apple Corp', '90211', 'AAPL'),
  ('Microsoft Company', '90212', 'MSFT');

INSERT INTO companies (name, data) VALUES
  ('Berkshire', '{"tickers": ["BRKA", "BRKB"]}'),
  ('Apple', '{"tickers": ["AAPL"]}'),
  ('Microsoft', '{"tickers": ["MSFT"]}');

SELECT * FROM (SELECT name, cusip, ticker FROM cusips) c,
  LATERAL (
   SELECT jsonb_array_elements(data->'tickers') AS ticker
   FROM companies
   WHERE ticker::TEXT = 'BRKA'
  ) cc
WHERE c.cusip = '90210'

This query returns:

name        cusip   ticker  ticker
Berkshire   90210   BRKA    "BRKA"
Berkshire   90210   BRKA    "BRKB"
Berkshire   90210   BRKA    "AAPL"
Berkshire   90210   BRKA    "MSFT"

I would like one row, with the name, cusip, and all the data for the company (in this case it would just be the tickers)...e.g.:

name        cusip   data
Berkshire   90210   {"tickers": ["BRKA", "BRKB"]}

EDIT: While I could join on the company name the names are oftentimes different between the tables so I must join on the ticker.

http://sqlfiddle.com/#!17/fa376/2

Upvotes: 0

Views: 336

Answers (2)

Nick
Nick

Reputation: 147246

It seems you just want a simple JOIN between the tables:

SELECT c.name, c.cusip, cc.data
FROM cusips c
JOIN companies cc ON cc.name= c.name
WHERE c.cusip = '90210'

Output:

name        cusip   data
Berkshire   90210   {"tickers": ["BRKA", "BRKB"]}

Demo on SQLFiddle

If you have to match on the tickers because the name columns may not match for some reason, you can use @> operator to check if the ticker exists in the tickers array:

SELECT c.name, c.cusip, cc.data
FROM cusips c
JOIN companies cc ON cc.data->'tickers' @> to_jsonb(c.ticker)
WHERE c.cusip = '90210'

Output:

name        cusip   data
Berkshire   90210   {"tickers": ["BRKA", "BRKB"]}

Demo on SQLFiddle

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271023

Nick's answer seems like the sanest answer. If for some reason you wanted to deconstruct the JSON and reconstruct it, you could use:

SELECT c.name, c.cusip, c.ticker, 
       jsonb_build_object('tickers', jsonb_agg(cc.ticker))
FROM cusips c CROSS JOIN LATERAL
     (SELECT jsonb_array_elements(co.data->'tickers') AS ticker
      FROM companies co
      WHERE co.name = c.name
     ) cc
WHERE c.cusip = '90210'
GROUP BY c.name, c.cusip, c.ticker;

Here is the corresponding db<>fiddle.

Upvotes: 2

Related Questions