CODEWITHSUNDEEP
sqlpresto
Edamame
Edamame

Reputation: 25386

Presto: Extract values from a string column, which is similar to map, but not a map

I have a table:

Name  pets
--------------
Andy  {"cat":2, "dog":1, "bird":4 ,xxx}
John  {"dog":3, "cat":1, "bird":{}, uyx}
Mary  {"dog":2, "duck":{}, "cat":1, zzz}

The pets column is a map, but the table creator made is a string, with some additional characters there. So I can not use cast(json_parse(pets) as map(varchar, varchar)) AS m.

In this case, if I want to find out the value of "cat", how do I do that? Thank you!

Upvotes: 0

Views: 1212

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270763

You can use regexp_extract():

select t.*,
       regexp_extract(pets, '"cats":([^,]*)', 1)
from t;

Upvotes: 3

Related Questions