Create view having nested json text from a table - Postgres

Question

Say, I have the following postgres table,

> select * from t;
+----+---------+-----------+---------+---------+
| id | a       | b         | c       | d       |
+----+---------+-----------+---------+---------+
| 1  | value_a | value_b   | value_c | value_d |
+----+---------+-----------+---------+---------+
...
+----+---------+-----------+---------+---------+
|... | ...     | ...       | ...     | ...     |
+----+---------+-----------+---------+---------+

Now I want to create a view from this table which is capable of storing data of following format having nested json text.

And this should be grouped by id column.

{
  "id": 1,
  "a": "A",
  "x":"{\"k1\":\"value_b\",\"k2\":\"{\"k21\":\"value_c\",\"k22\":\"value_d\"}\"}"
}

I have no idea how can I achieve this. Anyone there to write me a postgres sql query for this.

Do you expect one row in the view for each row in the source table, or there is some aggregation involved (in which case you would need to show more than one row of data)? — GMB
– GMB, Commented Mar 30, 2020 at 10:20
@GMB You can assume one row in the view for each row in the source table for now. — Shudipta Sharma
– Shudipta Sharma, Commented Mar 30, 2020 at 10:43
@GMB What if I my nested json field is an array from another table which I have to get by a query? I have updated the question, please provide a suggestion/answer. And also keep your original answer. That was also a part of my question. — Shudipta Sharma
– Shudipta Sharma, Commented Mar 30, 2020 at 12:14
It is not a good practice to substiantially update a question after answers were posted. I would suggest accepting the answer (if you believe that it properly responded your original question) and asking a new question, providing proper new sample data and expected results. — GMB
– GMB, Commented Mar 30, 2020 at 12:54

GMB · Accepted Answer · 2020-03-30 10:50:53Z

1

You can use the json(b)_build_* functions to generate json(b):

create view myview as
select 
    id,
    a,
    jsonb_build_object(
        'k1', b,
        'k2', jsonb_build_object(
            'k21', c,
            'k22', d
        )
    ) as x
from mytable

If you want the whole row as a single json object:

create view myview as
select jsonb_build_object(
    'id', id
    'a', a,
     'x', jsonb_build_object(
        'k1', b,
        'k2', jsonb_build_object(
            'k21', c,
            'k22', d
        )
    )
) val
from mytable

answered Mar 30, 2020 at 10:50

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

Create view having nested json text from a table - Postgres

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