Select value for a key in json in specific column in MySQL

Question

In MySQL, how do I select ONLY the values for nationality key in the jsonInfo column for all rows?

userid  displayName jsonInfo
11  John Nakamura   {"nationality":"ROK", "build": "average", "height":"511", "eyeColor":"brown", "ethnicity":"Asian"}
12  Jose Hernandez  {"nationality": "El Salvador", "build": "average", "height":"510", "eyeColor":"brown", "ethnicity":"Latino"}
13  Bobby Simmons   {"nationality": "USA", "build": "big", "height":"601", "eyeColor":"blue", "ethnicity":"Caucasian"}

Probable duplicate of https://stackoverflow.com/questions/15701579/how-to-retrieve-json-data-from-mysql — Salvino D'sa, Sep 01 '21 at 03:48
Think what you're looking for is `json_extract(jsonInfo, '$.nationality') AS Nationality` — Salvino D'sa, Sep 01 '21 at 03:49
Does this answer your question? [How to retrieve JSON data from MySQL?](https://stackoverflow.com/questions/15701579/how-to-retrieve-json-data-from-mysql) — ggordon, Sep 01 '21 at 04:43

score 0 · Accepted Answer · answered Sep 01 '21 at 03:50

0

SELECT jsonInfo->>'$.nationnality' FROM tbl

answered Sep 01 '21 at 03:50

Soheil Rahsaz

719
7
22

Select value for a key in json in specific column in MySQL

1 Answers1