PostgreSQL - Convert rows into JSON key/value pair

Question

I have a simple table like this:

Key_1 | Key_2 | Value

Rows have multiple combinations of key_1 and key_2.

I want to put rows from this table into a JSON structure like this:

{
    "myJSON": {
        "key_1": {
            "key_2": value,
            "key_2": value
        }
        "key_1": {
            "key_2": value,
            "key_2": value
        }
}

I am able to get the output into rows using this query:

SELECT
    key_1,
    jsonb_object_agg(key_2,value)
FROM table
GROUP BY key_1
ORDER BY key_1;

This produces output like this:

key_1 | {"key_2":value, "key_2": value}
key_1 | {"key_2":value, "key_2": value}

However I can't work out the last step to get those returned rows into a JSON object structure as per above. I can't use another aggregation function because it complains about nesting.

Is there a way I can do it, or is it something I'll have to do with the result set outside of the database?

DarrenM · Accepted Answer · 2018-06-26 08:17:11Z

1

This does what I want:

WITH t AS (
  SELECT
      key_1,
      jsonb_object_agg(key_2,value) key_pair
  FROM table
  GROUP BY key_1
  ORDER BY key_1
)
SELECT jsonb_object_agg(key_1, key_pair)
FROM t

answered Jun 26, 2018 at 8:17

DarrenM

414 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL - Convert rows into JSON key/value pair

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