Dismiss
Announcing Stack Overflow Documentation

We started with Q&A. Technical documentation is next, and we need your help.

Whether you're a beginner or an experienced developer, you can contribute.

Sign up and start helping → Learn more about Documentation →

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 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?

share|improve this question
    
"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
up vote 1 down vote accepted

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)    
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.