Search code examples
sqlpostgresqlpostgresql-9.4jsonb

How do I include rows that don't meet the criteria?

I know I'm probably just thinking about this wrong. I have the following structure:

  CREATE TABLE mytable (
  id       serial PRIMARY KEY
, employee text UNIQUE NOT NULL
, data     jsonb
);

And the following data:

INSERT INTO mytable (employee, data)
VALUES
 ('Jim', '{"sales": [{"value": 10, "yr": "2010"}, {"value": 5, "yr": "2011"}, {"value": 40, "yr": "2012"}]}'),
 ('Rob', '{"sales": [{"value": 10, "yr": "2009"}, {"value": 5, "yr": "2010"}, {"value": 41, "yr": "2011"}]}')

I'm trying to return all the employees and the "value" of their sales in 2012. If there is no sales in 2012 then return "No Data". I have:

SELECT id, employee, 
coalesce((SELECT s.value AS value FROM mytable, jsonb_to_recordset(mytable.data->'sales') AS s(yr text, value float)
WHERE s.yr='2012'), 0) AS b FROM mytable

I get:

id |employee |b
53 |Jim      |40 
54 |Rob      |40

The value is wrong for 'Rob'. It should be 'No Data'. (I am using 0 as the 2nd parameter for coalesce as I get an error "invalid input syntax for type double precision: 'No Data'"

Solution

  • The key element is to use LEFT JOIN LATERAL instead of the implicit CROSS JOIN LATERAL as which the short notation with just a comma is interpreted.

    The query can simply be:

    SELECT t.id, t.employee, s.*
FROM   mytable t
LEFT   JOIN LATERAL jsonb_to_recordset(t.data->'sales')
                 AS s(yr int, value int) ON s.yr = 2012;

    We can conveniently pick sales with yr = 2012 right away without losing employees from the result.

    To prettify with 'NO Data' the value column must be a matching string type:

    SELECT t.id, t.employee
     , COALESCE(s.yr, 2012) AS yr
     , COALESCE(s.value, 'No Data') AS value
FROM   mytable t
LEFT   JOIN LATERAL jsonb_to_recordset(t.data->'sales')
                 AS s(yr int, value text) ON s.yr = 2012;

    Building on these missing (likely) details:

    • There is exactly one row per employee in the table: empoyee is UNIQUE NOT NULL.

    • Each employee can have 0-n years of sales in data - data can be NULL.

    • yr and value store valid integer numbers. Else adapt the type.

    Proper table definition:

    CREATE TABLE mytable (
  id       serial PRIMARY KEY
, employee text UNIQUE NOT NULL
, data     jsonb
);

    What's a LATERAL JOIN?

    As per request in comment, these links should help, especially the first for beginners: