Extract array from varchar in PrestoSQL

user_15240096 0
Viewed 271

I have a VARCHAR field like this:

[
  {
    "config": 0,
    "type": "0
  },
  {
    "config": x,
    "type": "1"
  },
  {
    "config": "",
    "type": ""
  },
  {
    "config": [
      {
        "address": {},
        "category": "",
        "merchant": {
          "data": [
            10,12,23
          ],
          "file": 0
        },
        "range_id": 1,
        "shop_id_info": null
      }
    ],
    "type": "new"
  }
]

And I need to extract merchant data from this. Desirable output is:

10

12

23

Please advise. I keep getting Cannot cast VARCHAR to array/unnest type VARCHAR

presto sql
1 Answers

You can try using json path $.*.config.*.merchant.data.* but if it does not work for you (as for me in Athena version, where arrays in json path are not supported well) you can cast your json to ARRAY(JSON) and do some manipultaions from there (needed to fix your JSON a little bit):

Test data:

WITH dataset AS (
    SELECT * FROM (VALUES   
        (JSON '[
  {
    "config": {},
    "type": "0"
  },
  {
    "config": "x",
    "type": "1"
  },
  {
    "config": "",
    "type": ""
  },
  {
    "config": [
      {
        "address": {},
        "category": "",
        "merchant": {
          "data": [
            10,12,23
          ],
          "file": 0
        },
        "range_id": 1,
        "shop_id_info": null
      }
    ],
    "type": "new"
  }
]')
        
 ) AS t (json_value))

And query:

SELECT flatten(
    transform(
        flatten(
            transform(
                CAST(json_value AS ARRAY(JSON))
                , json_object -> try(CAST(json_extract(json_object, '$.config') AS ARRAY(JSON))))),
        json_config -> CAST(json_extract(json_config, '$.merchant.data') as ARRAY(INTEGER))))
FROM dataset

Which will give you array of numbers:

_col0
[10, 12, 23]

And from there you can continue with unnest and so on if needed.

user_25012790
Related