-2

I am using MySQL version 5.6.41-84.1-log, I want to fetch single column of JSON data

example: I want to fetch date only

 id | amount_detail
    ----------------
    1  | {"1":{"amount":"6000","date":"2020-01-21","amount_discount":"0","amount_fine":"0","description":"balance 4000 Collected By: Super Admin","payment_mode":"Cash","inv_no":1}, "2":{"amount":"3000","date":"2020-01-22","amount_discount":"1000","amount_fine":"0","description":"full paid Collected By: Super Admin","payment_mode":"Cash","inv_no":2}}

anyone give me the solution

0

Use common string functions and extract the value you need.

SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(amount_detail, '"date":"', -1), '"', 1) AS date_from_JSON
FROM sourcetable
  • above query is executed correctly but I stored like this i want fetch "1" or "2" in JSON value {"1":{"amount":"6000","date":"2020-01-21","amount_discount":"0","amount_fine":"0","description":"balance 4000 Collected By: Super Admin","payment_mode":"Cash","inv_no":1}, "2":{"amount":"3000","date":"2020-01-22","amount_discount":"1000","amount_fine":"0","description":"full paid Collected By: Super Admin","payment_mode":"Cash","inv_no":2}} – Mini TechNos Jan 23 at 5:07
  • @MiniTechNos Do not add the info as a comment - add (update) it into the question text. And when you show some data then show the result which you need to extract from. – Akina Jan 23 at 5:15
0

Native JSON support wasn't introduced until MySQL 5.7 so you're out of luck if you're still running [the six-year-old] version 5.6.

I have to question why this is being stored as JSON in the first place.

This looks to me like perfectly normal, relational-style Data that could be stored in a conventional table structure:

create table table1 
( id int not null auto_increment 
, amount decimal( 10, 2 ) 
, date1 date 
, discount decimal( 10, 2 )
, fine decimal ( 10, 2 ) 
, description varchar( 200 )
, payment_code varchar( 40 )    
, inv_no int not null 
, primary key ( id ) 
); 

Not the answer you're looking for? Browse other questions tagged or ask your own question.