CODEWITHSUNDEEP
mysqlsqljson
mohan111
mohan111

Reputation: 8865

How to extract the information from JSon in MYSQL

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 .

Upvotes: 0

Views: 48

Answers (1)

GMB
GMB

Reputation: 222702

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

Upvotes: 1

Related Questions