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

Question

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

Answer 1

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