How to create an empty JSON object in postgresql?

Question

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). Simplified data-model

Now there is a query to retrieve all users (name) with all their attributes (data). The attributes are returned as JSON object 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 (   JOIN 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 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 just 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 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 and not an array.

2. Split up JSON creation and return 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.

Qustion

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 which would solve my problem?

Answer 1

Use left join with coalesce(). As default value use '{}'::json.

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 1     ) d on m.id = d.fk_id;    name   |                data                 ---------+------------------------------------  Florian | { "age" : "25" }  Marcus  | { "age" : "25", "color" : "blue" }  Thomas  | {} (3 rows)