How to generate JSON in PostgreSQL with conditional keys

Question

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.

Bergi · Accepted Answer · 2019-10-09 18:52:11Z

2

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

edited Oct 9, 2019 at 18:52

answered Oct 9, 2019 at 18:41

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mad Wombat Over a year ago

This looks promising, but I run into a type problem in the values clause, since field1, field2 and field3 are different types. VALUES types character, varying and integer cannot be matched

Bergi Over a year ago

@MadWombat ah, if your fields have different types then you'll need to cast them to json beforehand. Or just do the filtering afterwards like in @Klin's answer

Collectives™ on Stack Overflow

How to generate JSON in PostgreSQL with conditional keys

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related