PHP Query Search In Table For Json Value

Question

I wonder if I can achieve the following with plain PHP:

I want to retrieve a row in a database based on a value that is in a json string. The string looks like the following:

{
   "ext":{
      "pdf":3
   },
   "count":3
}

I want to retrieve all the rows where PDF equals 4.

Is this something that is possible in PHP? To make a query and only search for a Json Value. Like this:

SELECT id FROM table WHERE ext->pdf = 4

I tried to search it for myself only I keep getting results of webpages that discuss encoding query results to Json instead of what I am trying to achieve.

Is that JSON string a value in your database or user input that you want to use find non-JSON data in your db? — simbabque
– simbabque, Commented Aug 10, 2015 at 14:27

Prasad Paranjape · Accepted Answer · 2015-08-10 12:44:46Z

1

If I understand your question correctly then you already know the ext i.e. it is going to be "pdf" every time. Considering that you can build your query as follows:

$json = '{
   "ext":{
      "pdf":3
   },
   "count":3
}';

$data = json_decode($json);

SELECT id FROM table WHERE pdf = $data['ext']['pdf'];

eg. SELECT id FROM table WHERE pdf = 3;

If you are not sure of the extension then you can try something as follows:

$where = '';
foreach ($data['ext'] as $ext => $value)
{
    $where .= ' ' . $ext . ' = ' . $value . ' OR';
}

$where = rtrim($where, 'OR');

$query = 'SELECT id FROM table WHERE] . $where;

decoding your json to Array is not necessary, but that's just my way of doing it.

answered Aug 10, 2015 at 12:44

Prasad Paranjape

1518 bronze badges

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

Comments

Nillus · Accepted Answer · 2015-08-10 14:37:23Z

1

No, it is not possible. Json data in relational dbs are not parsed by db engines.

EDIT: Read comments contributions about newest support for this feature

You are free to store json into db, but you accept, doing so, to give up using json data as if stored in columns of db as other relational db data. Usually you store json data in db only if you know you will not need to search or update directly based on some data contained in json structure, and that json data will not need to be updated the way data stored in "normal" columns is in queries

You could:

1 - use another column of your db table to store pdf value (or any other json data that could be relevant in queries) and use it in queries, making sure pdf column and its relative json be always synchronized;

2 - manually parse the json string with db native string or regexp functions, but this is very expensive especially for large tables, or large json. This could make your query time very large;

3 - use a non relational db, like mongoDb, where you can store data differently, if you need to perform such duties very often

edited Aug 10, 2015 at 14:37

answered Aug 10, 2015 at 12:36

Nillus

1,2191 gold badge15 silver badges34 bronze badges

2 Comments

simbabque Over a year ago

Actually there are several relational database systems that support accessing JSON data structures. MySQL is going to be one of them when 5.7 is released. mysqlserverteam.com/…

Nillus Over a year ago

I didn't know. However I think it is not the case here, and I am concerned about performance in that case. Indeed, using regular data structures for a relational db should be always better

Collectives™ on Stack Overflow

PHP Query Search In Table For Json Value

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related