PostgreSQL return JSON objects as key-value pairs

Question

A PostgreSQL instance stores data in JSONB format:

CREATE TABLE myschema.mytable (
    id   BIGSERIAL PRIMARY KEY,
    data JSONB     NOT NULL
)

The data array might contain objects like:

{
  "observations": {
    "temperature": {
      "type": "float",
      "unit": "C",
      "value": "23.1"
    },
    "pressure": {
      "type": "float",
      "unit": "mbar",
      "value": "1011.3"
    }
  }
}

A selected row should be returned as key-value pairs in a format like:

temperature,type,float,value,23.1,unit,C,pressure,type,float,value,1011.3,unit,mbar

The following query returns at least each object, while still JSON:

SELECT id, value FROM mytable JOIN jsonb_each_text(mytable.data->'observations') ON true;
1 | {"type": "float", "unit": "mbar", "value": 1140.5}
1 | {"type": "float", "unit": "C", "value": -0.9}
5 | {"type": "float", "unit": "mbar", "value": "1011.3"}
5 | {"type": "float", "unit": "C", "value": "23.1"}

But the results are splitted and not in text format.

How can I return key-value pairs of all objects in data?

Answer 1

This will flatten the json structure and effectively just concatenate the values, along with the top-level key names (e.g. temperature and pressure), for the expected "depth" level. See if this is what you had in mind.

SELECT
    id,
    (
      SELECT STRING_AGG(conc, ',')
      FROM (
        SELECT CONCAT_WS(',', key, STRING_AGG(value, ',')) AS conc
        FROM (
          SELECT key, (jsonb_each_text(value)).value
          FROM jsonb_each(data->'observations')
        ) AS x
        GROUP BY key
      ) AS csv
    ) AS csv
FROM mytable

Result:

| id  | csv                                                 |
| --- | --------------------------------------------------- |
| 1   | pressure,float,mbar,1011.3,temperature,float,C,23.1 |
| 2   | pressure,bigint,unk,455,temperature,int,F,45        |

https://www.db-fiddle.com/f/ada5mtMgYn5acshi3WLR7S/0