Postgres: How to flatten nested json data

Question

New to postgres. I am trying to flatten this nested json data and following this example (Flatten nested JSON structure in PostgreSQL) but I'm stuck. The result so far seems to be a cross product combination of timestamp and amount that I can't seem to resolve. The data I'm trying to end up with should look like this.

shard	prefix	id	timestamp	amount
0	0	12345	1703119840677243794	11111
0	0	12345	1703206309696691698	22222

SELECT 
   shard
   , prefix
   , id
   , field
   , value
   --, fields
FROM (
  SELECT 
        shard, prefix, id
        , json_object_keys(json_array_elements(fields)) as field
        , json_each_text(json_array_elements(fields)) as value
        --, fields
  FROM (
    SELECT
        (datasets -> 'shard')::text as shard,
        (datasets -> 'prefix')::text as prefix,
        (datasets -> 'id')::text as id,
        (datasets -> 'amounts' -> 'fields') as fields
            FROM(
                 SELECT 
                    json_array_elements(json) as datasets
                  FROM (
                    SELECT '[
              {
                "amounts": {
                  "fields": [
                    {
                      "amount": 11111,
                      "timestamp": "1703119840677243794"
                    },
                    {
                      "amount": 22222,
                      "timestamp": "1703206309696691698"
                    }
                  ]
                },
                "shard": 0,
                "prefix": 0,
                "id": 12345
              }
            ]'::json
        )d)c)b)a;

Any help would be much appreciated.

consuela · Accepted Answer · 2023-12-22 21:55:51Z

I figured it out. json_extract_path is the key

SELECT 
        shard, prefix, id        
        , json_extract_path(json_array_elements(fields), 'amount') AS amount
        , json_extract_path(json_array_elements(fields), 'timestamp') AS timestamp
  FROM (
    SELECT
            (datasets -> 'shard')::text as shard,
            (datasets -> 'prefix')::text as prefix,
        (datasets -> 'id')::text as id,
        (datasets -> 'amounts' -> 'fields') as fields
            FROM(
          SELECT 
                      json_array_elements(json) as datasets
                  FROM (
                    SELECT '[
              {
                "amounts": {
                  "fields": [
                    {
                      "amount": 11111,
                      "timestamp": "1703119840677243794"
                    },
                    {
                      "amount": 22222,
                      "timestamp": "1703206309696691698"
                    }
                  ]
                },
                "shard": 0,
                "prefix": 0,
                "id": 12345
              }
            ]'::json
        )d)c)b;

Collectives™ on Stack Overflow

Postgres: How to flatten nested json data

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related