Extract complex nested JSON array in Presto

Question

I have a complex JSON object like this:

  {
    "item_detail": [
      {
        "itemid": "4702385896",
        "modelid": "8307307322",
        "quantity": "1"
      },
      {
        "itemid": "3902478595",
        "modelid": "8306561848",
        "quantity": "1"
      },
      {
        "itemid": "3409528897",
        "modelid": "10922686275",
        "quantity": "1"
      },
      {
        "itemid": "4702385896",
        "modelid": "8307307323",
        "quantity": "1"
      }
    ],
    "shopid": "128449080"
  },
  {
    "item_detail": [
      {
        "itemid": "7906381345",
        "modelid": "9745718882",
        "quantity": "1"
      },
      {
        "itemid": "6710792892",
        "modelid": "11474621623",
        "quantity": "1"
      }
    ],
    "shopid": "36121097"
  }
]

I am struggling in extracting all (itemid, shopid) into rows in Presto. My wanted outcomes are:

itemid     | shopid
-----------+-------
4702385896 | 128449080
3902478595 | 128449080
3409528897 | 128449080
4702385896 | 128449080
7906381345 | 36121097
6710792892 | 36121097

I have used CROSS JOIN UNNEST and TRANSFORM to get the result with no luck. Does anyone have a solution for this?

So many thanks in advance!

Please add what have you tried so far

blackbishop
– blackbishop

2019-12-29 18:50:00 +00:00
Commented Dec 29, 2019 at 18:50 — blackbishop
– blackbishop, Commented Dec 29, 2019 at 18:50

Piotr Findeisen · Accepted Answer · 2019-12-29 18:52:06Z

4

Use json_extract with cast to array and unnest, like this:

presto:default> SELECT
             ->  json_extract_scalar(item_detail, '$.itemid') itemid,
             ->  json_extract_scalar(shopping, '$.shopid') shopid
             -> FROM t
             -> CROSS JOIN UNNEST(CAST(my_json AS array(json))) AS x(shopping)
             -> CROSS JOIN UNNEST(CAST(json_extract(shopping, '$.item_detail') AS array(json))) AS y(item_detail)
             -> ;
             ->
   itemid   |  shopid
------------+-----------
 4702385896 | 128449080
 3902478595 | 128449080
 3409528897 | 128449080
 4702385896 | 128449080
 7906381345 | 36121097
 6710792892 | 36121097
(6 rows)

(verified on Presto 327)

BTW if any of the arrays may be empty or missing, I recommend using LEFT JOIN UNNEST ... ON true instead of CROSS JOIN UNNEST (this requires a decent Presto version):

SELECT
 json_extract_scalar(item_detail, '$.itemid') itemid,
 json_extract_scalar(shopping, '$.shopid') shopid
FROM t
LEFT JOIN UNNEST(CAST(my_json AS array(json))) AS x(shopping) ON true
LEFT JOIN UNNEST(CAST(json_extract(shopping, '$.item_detail') AS array(json))) AS y(item_detail) ON true;

answered Dec 29, 2019 at 18:52

Piotr Findeisen

21k4 gold badges62 silver badges86 bronze badges

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

2 Comments

vj sreenivasan Over a year ago

presto:tiny> with t1(my_json) AS (VALUES '[{"item_detail": [{"itemid": "470238", "modelid": "8307", "quantity": "1"} ], "shopid": "1284"}, {"item_detail": [{"itemid": "790638", "modelid": "9745", "quantity": "1"}], "shopid": "3612"} ]') SELECT json_extract_scalar(item_detail, '$.itemid') itemid, json_extract_scalar(shopping, '$.shopid') shopid FROM t1 CROSS JOIN UNNEST(CAST(my_json AS array(json))) AS x(shopping)CROSS JOIN UNNEST(CAST(json_extract(shopping, '$.item_detail') AS array(json))) AS y(item_detail);

Query failed: line 6:19: Cannot cast varchar(496) to array(json)

Piotr Findeisen Over a year ago

@vjsreenivasan replace VALUES '[{"item_detail.... with VALUES JSON '[{"item_detail.... to construct json type literal, instead of a string (varchar) literal.

Collectives™ on Stack Overflow

Extract complex nested JSON array in Presto

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related