• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to retrieve data from json column?

#1
I’m a beginner with codeigniter. I’m trying to retrieve data from json column of my sql db.
 The db structure is like this

Code:
DB NAME : order
——————————————————---------------------------------------------------
Id    | description
——————————————————————————————————-----------------------------------
1      | {“pc”: [{ “brand”: “name”}], “description”: [“LL”, “DC”]}
————————————————————————————————————---------------------------------
2      |  {“pc”: [{ “brand”: “name”}], “description”: [“TR”, “KK”]}
————————————————————————————————————---------------------------------
3      |  {“pc”: [{ “brand”: “name”}], “description”: [“DC”, “JJ”]}
————————————————————————————————————---------------------------------

For example, I want to retrieve all instances that has order = dc

Code:
$data = $this->db->select($select)->from(‘order’)
            ->where(“description”, “DC”) ->get()->result();
Reply

#2
@jaune,

What field type is description? Is it a string? if so, then you could search it like a string. Using the LIKE condition.

I also did a search online and found numerous articles that should be able to assist you...
https://scotch.io/tutorials/working-with-json-in-mysql
https://stackoverflow.com/questions/7602...-via-mysql
Reply

#3
(09-23-2019, 05:28 PM)php_rocs Wrote: @jaune,

What field type is description?  Is it a string?  if so, then you could search it like a string. Using the LIKE condition.

I also did a search online and found numerous articles that should be able to assist you...
https://scotch.io/tutorials/working-with-json-in-mysql
https://stackoverflow.com/questions/7602...-via-mysql
Thank you for your help, but I have the same a issue. Because if I write

SELECT id FROM order 
WHERE JSON_EXTRACT(description, "$.description[1]”) = 'DC'

but i f I write 

SELECT id FROM order WHERE JSON_EXTRACT(description, "$.description[*]”) = 'DC'

[*]It doesn't work. Because I'm trying to retrieve all instances, so I can't know for each instance which is the index of the array description.
Reply

#4
@jaune,

Maybe, when I get a minute or two I can setup an online database with test data and see what we can come up with. Do you have any more test data? Also, what version of CI, MySQL and PHP are you using?
Reply

#5
Thank you for your help!
The settings are:


CI = 3.1.8
libmysql - 5.6.43
Php 7.2.7


I have attached a mockup .sql file.
Reply

#6
@jaune,

I don't see the mockup sql file..
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.