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?

Can you show an example of the desired output with columns as well, as I can't tell if that's one big string or a bunch of column names. — 404
– 404, Commented Mar 25, 2019 at 13:22
Actually, I just need to output the fetched rows in CSV format. The keys are not required, so temperature,float,23.1,C,pressure,float,1011.3,mbar would be sufficient, too. — laserbrain
– laserbrain, Commented Mar 25, 2019 at 13:28
Do you know that it will always contain "temperature" and "pressure", or could you have keys in there with any names? — 404
– 404, Commented Mar 25, 2019 at 13:40

404 · Accepted Answer · 2019-03-25 13:57:16Z

4

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

answered Mar 25, 2019 at 13:57

404

8,7522 gold badges33 silver badges53 bronze badges

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

6 Comments

laserbrain Over a year ago

Thank you very much, works like a charm. I’ll figure out the details of your query by my own.

laserbrain Over a year ago

Is it also possible to return all elements as an SQL array, instead of a concatenated string? Wouldn’t it be even easier?

404 Over a year ago

@laserbrain Sort of, but if you put all of those comma delimited values in a single array then you lose ordering. Depends on what you do with it I guess; if you keep them in the array then any initial ordering when creating the array will be maintained, but as soon as you unnest it it's no longer guaranteed unless you reorder it, and any ordering will push e.g. "pressure" into some unknown index which may have no relation to its values.

laserbrain Over a year ago

Thanks for the clarification.

laserbrain Over a year ago

Is it further possible to include id in the returned element csv using STRING_AGG, e.g. 2,pressure,bigint,unk,455,temperature,int,F,45?

|

Collectives™ on Stack Overflow

PostgreSQL return JSON objects as key-value pairs

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related