mySql - extract key from array of objects

Question

Given an array of jsons I want to extract a given key from object object

e.g:

extract 'Name' key

'[{"Name": "Homer", "Age": 39}, {"Name": "Marge", "Age": 40}]' --->["Homer" , "Marge"]

My attempt

SELECT JSON_EXTRACT('[{"Name": "Homer", "Age": 39}, 
                      {"Name": "Marge", "Age": 40}]','$.Name');

returns null

Im using mysql 5.7.23

Barbaros Özhan · Accepted Answer · 2021-02-07 09:31:04Z

1

You just need square bracketed notation such as '$[*].Name' while formatting the second argument as JSON value is nested within them

SELECT JSON_EXTRACT('[{"Name": "Homer", "Age": 39}, 
                      {"Name": "Marge", "Age": 40}]','$[*].Name') AS names

answered Feb 7, 2021 at 9:25

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

1 Answer 1