Welcome Guest, Not a member yet? Register   Sign In
How to retrieve data from json column?
#1

(This post was last modified: 09-23-2019, 12:04 PM by jaune.)

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

(This post was last modified: 09-24-2019, 12:40 PM by jaune.)

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




Theme © iAndrew 2016 - Forum software by © MyBB