sql - Extract keysvalues from a nested JSON object stored as string in Presto? - Stack Overflow

I am getting below payload as string and need to extract all the values for key "id" .

I am writing like this:

JSON_EXTRACT_SCALAR(api_response, '$.0.id') as extract_id

But it is extracting only for "0"th index leaving rest of the id value. Can someone please guide me how to extract all the ids here.

api_response:

{
    "0":{
        "id": 1, 
        "value":"xxx" ,  
        "author":{
            "name":"some name",
            "surname":"some surname"
         },
    }, 
    "1":{
        "id":2, 
        "value":"yyy",
        "author":{
            "name":"some name1",
            "surname":"some surname1"
         },
    }
    "3":{
        "id":3, 
        "value":"zzz",
        "author":{
            "name":"some name2",
            "surname":"some surname2"
         },
    }
}

I am getting below payload as string and need to extract all the values for key "id" .

I am writing like this:

JSON_EXTRACT_SCALAR(api_response, '$.0.id') as extract_id

But it is extracting only for "0"th index leaving rest of the id value. Can someone please guide me how to extract all the ids here.

api_response:

{
    "0":{
        "id": 1, 
        "value":"xxx" ,  
        "author":{
            "name":"some name",
            "surname":"some surname"
         },
    }, 
    "1":{
        "id":2, 
        "value":"yyy",
        "author":{
            "name":"some name1",
            "surname":"some surname1"
         },
    }
    "3":{
        "id":3, 
        "value":"zzz",
        "author":{
            "name":"some name2",
            "surname":"some surname2"
         },
    }
}

• sql
• presto

Share Improve this question Follow edited Jan 7 at 0:49 Dale K 27.6k1515 gold badges5858 silver badges8383 bronze badges asked Jan 7 at 0:08 SDDSDD 1122 bronze badges 5

• I don't know Presto but try $.*.id as your json path expression. – gwcoffey Commented Jan 7 at 0:22
• Thanks @gwcoffey for your suggestions but * doesn't work in presto ,tried it is returning NULL – SDD Commented Jan 7 at 0:34
• Another stab in the dark haha: $..id. That (if it works) would grab the value of any property named id not just these top level objects. But if your JSON always looks like this maybe that is ok? – gwcoffey Commented Jan 7 at 0:37
• nope $..i also doesnt work , i tried with you * suggestion :-) – SDD Commented Jan 7 at 1:01
• 1 @gwcoffey , your first suggestion with slight tweak worked well. "JSON_EXTRACT(api_response, '$.*.content')" – SDD Commented Jan 7 at 1:45

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

JSON_EXTRACT(api_response, '$.*.content') works fine and extracts all the required id from the payload.

I don't think this is possible in Presto, but Trino (the successor project to Presto) supports the SQL standard JSON function. With Trino you can run the following:

SELECT json_query(
'{
    "0":{
        "id": 1,
        "value":"xxx" ,
        "author":{
            "name":"some name",
            "surname":"some surname"
         }
    },
    "1":{
        "id":2,
        "value":"yyy",
        "author":{
            "name":"some name1",
            "surname":"some surname1"
         }
    },
    "3":{
        "id":3,
        "value":"zzz",
        "author":{
            "name":"some name2",
            "surname":"some surname2"
         }
    }
}',
'lax $.*.id' WITH ARRAY WRAPPER);

This results in:

  _col0  
---------
 [1,2,3] 
(1 row)

Note, the example JSON you posed is invalid as it has trailing commas (after the author field), and is missign a comma after object 1. Without fixing the invalid JSON you get null or an error.