How to extract the information from JSon in MYSQL

Question

I have Sample Data

 set @j = '[{"id": 1, "title": "Mohan"}, 
            {"id": 2, "title": "Rama"}, 
            {"id": 3, "title": "IP Mana"}]';
  Select REPLACE(REPLACE(REPLACE(JSON_EXTRACT(@j, '$**.*'),"[",""),"]",""),'"','') AS Name_List from  tbl_employee

I'm getting Data Like this :

    NameList
 1, Mohan, 2, Rama, 3, IP Mana

I'm trying to get output like this :

NameList
 1 : Mohan, 2 : Rama, 3 : IP Mana

Can any one Suggest me .

@GMB MYSQL 8.0.13 but i need to execute in Lower versions also — mohan111
– mohan111, Commented Apr 22, 2020 at 13:01

GMB · Accepted Answer · 2020-04-22 13:11:36Z

1

In MySQL 8.0, you can use json_table() for this:

select id, title
from json_table(
    @j,
    '$[*]'
    columns (id int path '$.id', title varchar(20) path '$.title')
) t;

Or if you want the results as a scalar value:

select group_concat(id, ' : ', title separator ', ') res
from json_table(
    @j,
    '$[*]'
    columns (id int path '$.id', title varchar(20) path '$.title')
) t;

In earlier versions, you would typically use a table of numbers and json_extract():

select 
    json_extract(@j, concat('$[', n.n, '].id')) id,
    json_extract(@j, concat('$[', n.n, '].title')) title
from (select 0 n union all select 1 union all select 2 union all select 3) n
where json_extract(@j, concat('$[', n, ']')) is not null

edited Apr 22, 2020 at 13:11

answered Apr 22, 2020 at 13:06

GMB

224k25 gold badges103 silver badges151 bronze badges

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

8 Comments

mohan111 Over a year ago

it is there in the table column how can I implement this in table

GMB Over a year ago

@mohan111:

select t.id, t.title from mytable x cross join json_table(     x.j,     '$[*]'     columns (id int path '$.id', title varchar(20) path '$.title') ) t;

mohan111 Over a year ago

it is giving me error . I have Employee Column in that JSon Data is there . Select JSON_EXTRACT(@j, '$**.*') AS Name_List from tbl_employee

GMB Over a year ago

@mohan111: what is JSON_EXTRACT(@j, '$**.*') supposed to be? This does not come from the queries I provided you with.

mohan111 Over a year ago

I will check with above provided code and rearrange my script and get back to you

|

Collectives™ on Stack Overflow

How to extract the information from JSon in MYSQL

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related