CODEWITHSUNDEEP
sqljsonpostgresql
Mad Wombat
Mad Wombat

Reputation: 15105

How to generate JSON in PostgreSQL with conditional keys

I am generating JSON using a query that looks like this

SELECT 
    json_build_object(
        'key1', t1.field1,
        'key2', t1.field2,
        'key3', t1.field3
    )
FROM table1 as t1
WHERE ...

and I get results that look like this

{"key1": 123, "key2": "some string", "key3": 100}

or like this

{"key1": 123, "key2": "some string", "key3": null}

What I need is a way to make the second example drop the whole key instead of including the null value. That is, I want to somehow omit key3 when the value in field3 is null. And end up with this JSON

{"key1": 123, "key2": "some string"}

How do I go about this? I am running PostgreSQL 9.4, so some JSON operations are not available to me.

Upvotes: 1

Views: 1010

Answers (1)

Bergi
Bergi

Reputation: 664648

You can use json_object_agg:

SELECT 
    (SELECT json_object_agg(name, value)
     FROM (VALUES
        ('key1', to_json(t1.field1)),
        ('key2', to_json(t1.field2)),
        ('key3', to_json(t1.field3))
        ) AS props(name, value)
     WHERE value IS NOT NULL
    ) AS json
FROM table1 as t1
WHERE ...

Upvotes: 2

Related Questions