24

Datamodel

A person is represented in the database as a meta table row with a name and with multiple attributes which are stored in the data table as key-value pair (key and value are in separate columns). Datamodel Simplified data-model

Now there is a query to retrieve all users (name) with all their attributes (data). The attributes are returned as JSON objects in a separate column. Here is an example:

name      data
Florian   { "age":25 }
Markus    { "age":25, "color":"blue" }
Thomas    {}

The SQL command looks like this:

SELECT
  name,
  json_object_agg(d.key, d.value) AS data
FROM meta AS m
JOIN (
  SELECT d.fk_id, d.key, d.value AS value FROM data AS d
  ) AS d
ON d.fk_id = m.id
GROUP BY m.name;

Problem

Now the problem I am facing is, that users like Thomas which do not have any attributes stored in the key-value table, are not shown with my select function. This is because it does only a JOIN and no LEFT OUTER JOIN.

If I would use LEFT OUTER JOIN then I run into the problem, that json_object_agg try's to aggregate NULL values and dies with an error.

Approaches

1. Return an empty list of keys and values

So I tried to check if the key-column of a user is NULL and return an empty array so json_object_agg would create an empty JSON object.

But there is not really a function to create an empty array in SQL. The nearest thing I found was this:

select '{}'::text[];

In combination with COALESCE the query looks like this:

json_object_agg(COALESCE(d.key, '{}'::text[]), COALESCE(d.value, '{}'::text[])) AS data

But if I try to use this I get the following error:

ERROR:  COALESCE types text and text[] cannot be matched
LINE 10:     json_object_agg(COALESCE(d.key, '{}'::text[]), COALES...
                                                ^
Query failed
PostgreSQL said: COALESCE types text and text[] cannot be matched

So it looks like that at runtime d.key is a single value, not an array.

2. Split up JSON creation and return an empty list

So I tried to take json_object_agg and replace it with json_object which does not aggregate the keys for me:

json_object(COALESCE(array_agg(d.key), '{}'::text[]), COALESCE(array_agg(d.value), '{}'::text[])) AS data

But there I get the error that null value not allowed for object key. So COALESCE does not check that the array is empty.

Question

So, is there a function to check if a joined column is empty, and if yes return just a simple JSON object?

Or is there any other solution that would solve my problem?

klin
  • 112,967
  • 15
  • 204
  • 232
cansik
  • 1,924
  • 4
  • 19
  • 39
  • "it looks like at runtime d.key is a single value and not an array" - I'm not sure what you mean by "at runtime". If `d.key` is a column, then you must know what type it is. From the error message, it appears to be type `text`; so an empty value would be `''` (or `text ''` or `''::text` if you want to be explicit). – IMSoP Oct 23 '15 at 14:44
  • Yes that's right, it is just text. But I thought it would be a list of text's because I reference there the whole column. But that was my misunderstanding of the SQL syntax. So with **at runtime** I mean when the query will be executed. – cansik Oct 23 '15 at 14:53
  • Ah, yes, I see. As a clearer example that it's referencing a value not the whole column, consider that the following works: `Select sum(some_column + 1) From some_table` - the `+1` has to act on each value separately before bundling them up into the `sum`. – IMSoP Oct 23 '15 at 15:59

1 Answers1

38

Use left join and coalesce() with an empty json object '{}'::json as a default value.

select 
    name, 
    coalesce(d.data, '{}'::json) as data
from meta m
left join (
    select 
        fk_id, 
        json_object_agg(d.key, d.value) as data
    from data d
    group by fk_id
    ) d
on m.id = d.fk_id;

Test it in db<>fiddle.

klin
  • 112,967
  • 15
  • 204
  • 232