Get keys into JSON field in postgres

Question

I have this Json in a column af type jsonb in Postgres

{
    "coreTimes": {
        "TMP_CLINICAL_TIMES_ANESTHESIOLOGY_OUT_DATETIME": {
            "value": 1
        },
        "TMP_CLINICAL_TIMES_ANESTHESIOLOGY_IN_DATETIME": {
            "value": 2
        },
        "TMP_CLINICAL_TIMES_SURGICAL_OUT_DATETIME": {
            "value": 3
        },
        "TMP_CLINICAL_TIMES_ROOM_IN_DATETIME": {
            "value": 4
        },
        "TMP_CLINICAL_TIMES_ROOM_OUT_DATETIME": {
            "value": null
        },
        "TMP_CLINICAL_TIMES_BLOCK_OUT_DATETIME": {
            "value": null
        },
        "TMP_CLINICAL_TIMES_BLOCK_IN_DATETIME": {
            "value": null
        },
        "TMP_CLINICAL_TIMES_SURGICAL_IN_DATETIME": {
            "value": null
        }
    }
}

I need this result:

Column
{"value":1}
{"value":2}
{"value":3}
{"value":4}
{"value":null}
{"value":null}
{"value":null}

What postgres function can i use to get this result if json properties can change dynamically (the key 'coreTimes' is fixed)?

In oracle i have resolved

 SELECT res.*
 FROM sopinterventionsaux   ,
      JSON_TABLE ( operating_times, '$.coreTimes.*'
           COLUMNS (
              value VARCHAR2 ( 2000 ) FORMAT JSON PATH '$'
                    )
                ) res

Dmitry · Accepted Answer · 2019-11-28 17:08:13Z

2

The following works in PostgreSQL 12 which supports JSON path query

WITH T AS (SELECT $$
{
    "coreTimes": {
        "TMP_CLINICAL_TIMES_ANESTHESIOLOGY_OUT_DATETIME": {
            "value": 1
        },
        "TMP_CLINICAL_TIMES_ANESTHESIOLOGY_IN_DATETIME": {
            "value": 2
        },
        "TMP_CLINICAL_TIMES_SURGICAL_OUT_DATETIME": {
            "value": 3
        },
        "TMP_CLINICAL_TIMES_ROOM_IN_DATETIME": {
            "value": 4
        },
        "TMP_CLINICAL_TIMES_ROOM_OUT_DATETIME": {
            "value": null
        },
        "TMP_CLINICAL_TIMES_BLOCK_OUT_DATETIME": {
            "value": null
        },
        "TMP_CLINICAL_TIMES_BLOCK_IN_DATETIME": {
            "value": null
        },
        "TMP_CLINICAL_TIMES_SURGICAL_IN_DATETIME": {
            "value": null
        }
    }
}
$$::JSONB)
SELECT jsonb_path_query(jsonb, '$.coreTimes.*') FROM T;

 jsonb_path_query 
------------------
 {"value": 4}
 {"value": null}
 {"value": null}
 {"value": null}
 {"value": null}
 {"value": 3}
 {"value": 2}
 {"value": 1}
(8 rows)

answered Nov 28, 2019 at 17:08

Dmitry

3,78018 silver badges17 bronze badges

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

1 Comment

Abelisto Over a year ago

For versions before 12 it could be ... select value from t, jsonb_each(jsonb -> 'coreTimes')

Collectives™ on Stack Overflow

Get keys into JSON field in postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related