mysql select specified key_name and key_value from json encode field

Question

I want to display specified key_name and key_value of a field with mysql where record is encoded json format

id_user           dtl_master              date    
  1         {"el1":"2","el2":"3"}       2015/01/01
  2         {"el1":"2","el2":"3"}       2015/01/01 
  3         {"el1":"5","el2":"6"}       2015/01/01

My query to filter select is

SELECT id_user,dtl_master FROM tb_inputdata WHERE dtl_master REGEXP '"el2":"([^"]*)4([^"]*)"';

but it return :

 id_user           dtl_master            
      1         {"el1":"6","el2":"4"}

I wanted to display like this

 id_user              el2            
   1                   4

I know REGEXP not running on SELECT, but only on WHERE, anybody know how to SELECT only related key_name or key_value?

Thank You

well a have a ton of data, I should save each pair variable and value on same field, so json format is the solution right now — Reids Meke Meke
– Reids Meke Meke, Commented Jan 16, 2015 at 19:26
If your application manually encodes stuff and just throws it in the database, then your application logic itself also has to take care with unfolding it again. — mario
– mario, Commented Jan 16, 2015 at 19:27
Bad design, however just json_decode and use el2 when fetching the rows from the result. — AbraCadaver
– AbraCadaver, Commented Jan 16, 2015 at 19:31
Hi, I don't want display and parse it in application (php) for somereason (memory and efficiency), I tried to do this on mysql first — Reids Meke Meke
– Reids Meke Meke, Commented Jan 16, 2015 at 19:31

Shahar · Accepted Answer · 2015-01-16 19:50:23Z

1

I'd recommend going to NoSQL but one way you can extract it is by doing:

SELECT id_user, common_schema.extract_json_value(dtl_master,'/el2') AS el2 WHERE dtl_master REGEXP '"el2":"([^"]*)4([^"]*)"';

If you plan to get common_schema. Installation documentation here.

But of course you can always select using regex, by SELECTing a PREG_REPLACE() value. This isn't default in MySQL alone either; you'd need to get something like this.

edited Jan 16, 2015 at 19:50

answered Jan 16, 2015 at 19:37

Shahar

1,6772 gold badges12 silver badges18 bronze badges

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

6 Comments

mopo922 Over a year ago

Is common_schema default to MySQL, or is that something you have to add?

Shahar Over a year ago

@mopo922 It's something you have to add. If he's going to work with JSON's, he might as well.

Reids Meke Meke Over a year ago

thank you for answering,but it require additional stuff to be installed code.google.com/p/common-schema also extract_json_value() is CPU intensive according to documentation. I still waiting for another answer

mopo922 Over a year ago

Can you add instructions to get common_schema to your answer?

Shahar Over a year ago

@ReidsMekeMeke Best answer you'll receive is switch to NoSQL. Although I'm waiting as well.

|

Collectives™ on Stack Overflow

mysql select specified key_name and key_value from json encode field

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related