Using JSON_EXTRACT in WHERE clause using Laminas-Db

Question

I have a mysql table with a json datatype used to store json_encoded data:

id|author_id|date_inserted|comments
---------------------------------------------
1|32|2022-04-01|{"id": "343","some":"data"}
2|15|2022-02-15|{"id": "24","some":"data"}
3|24|2022-05-22|{"id": "995","some":"data"}

Using laminas-db, how is it possible to use JSON_EXTRACT in a where clause? For instance, when using a tablegateway, i would like to update the json data of a row using something like the following:

$this->tableGateway->update($data, [JSON_EXTRACT('comments', '$.id') = 24]);

John Crest · Accepted Answer · 2022-12-05 00:29:22Z

0

I was able to achieve it with the following:

 $commentId = 24;
 $where = new Where();
 $where->addPredicate(new Expression('JSON_EXTRACT(comments, "$.id") = ?', $commentId));
 return $this->tableGateway->update($data, $where);

answered Dec 5, 2022 at 0:29

John Crest

2611 gold badge4 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

Using JSON_EXTRACT in WHERE clause using Laminas-Db

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related