Wrap a Postgres Json Array in a Json object

Question

When I execute

SELECT array_to_json(array_agg(row_to_json(curriculum_overview))) 
FROM study.curriculum_overview;

On a predefined view I have I get something similar to this

[{"study_programme":"SE","semester":1,"module_code":"B1"},...}]

I want this array wrapped in an object, and possibly add some metadata like a timestamp to it, example:

{
    timeStamp: now,
    data: [{...},{...},{...}]
}

How is this possible using a query? Or do I have to do this in a client and modify the data after obtaining the query results?

I want to wrap the JSON Array that Postgres gives me in a JSON Object using a query. Similar to this stackoverflow.com/questions/46231422/… — Lehren
– Lehren, Commented Sep 28, 2017 at 12:11

klin · Accepted Answer · 2017-09-28 12:10:43Z

4

Use the function json_build_object(), e.g.:

SELECT 
    json_build_object(
        'timestamp', now(), 
        'metadata', 'some_value',
        'data', array_to_json(array_agg(row_to_json(co))))
FROM study.curriculum_overview co;

answered Sep 28, 2017 at 12:10

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

Wrap a Postgres Json Array in a Json object

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related