CODEWITHSUNDEEP
mysqlsqljsonmysql-5.7
yoty66
yoty66

Reputation: 744

mySql - extract key from array of objects

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

Upvotes: 0

Views: 876

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65198

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

Demo

Upvotes: 1

Related Questions