CODEWITHSUNDEEP
jsonpostgresql-9.4
DarrenM
DarrenM

Reputation: 41

PostgreSQL - Convert rows into JSON key/value pair

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?

Upvotes: 3

Views: 2438

Answers (1)

DarrenM
DarrenM

Reputation: 41

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

Upvotes: 1

Related Questions