0

I have a table of Products that also contains JSON datatype with JSON array - json_array() field.

For the example "Products" table includes the following:

ProductID
ProductName
ProductDesc
CreateDate
Price
HistoricalPrices (json)

I am appending the data using the follow query syntax:

    UPDATE Products 
SET    HistoricalPrices =
           json_array_append(HistoricalPrices, 
               '$', json_object('CreateDate', '2016-05-01', 'Price', 23.65)
           )
WHERE  ProductID = 1;

Example of structure of the JSON array:

> [
>     {
>         "CreateDate": "2016-05-01",
>         "Price": 12.34
>     },
>     {
>         "CreateDate": "2016-05-22",
>         "Price": 12.50
>     } ]

Would it be possible to search inside the JSON array for a specific productID. Like looking for specific prices in a date range ?

Dror
  • 1,262
  • 3
  • 21
  • 34
  • If you want to do this for one record, then another language is more suitable for this, like PHP. If you want to search products based on a condition inside these JSON values, then you are on the wrong track. You should design your database with a separate Historical Prices table, or use a *document* database, like MongoDB, which are stronger on dealing with nested structures. What exactly is your case? – trincot Jul 17 '16 at 16:02
  • [MySQL JSON Search Functions](https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html) might help, since I don't have latest MySQL, I cant test it on JSON Array. – Deepak Chaudhary Jul 17 '16 at 16:07
  • @trincot mysql 5.7 has json, for those brave souls that want it – Drew Jul 17 '16 at 16:16
  • @Drew, I know that. Not sure why you tell me that. ;-) My take is that languages connecting to MySql are more suitable for doing such things. The MySql JSON functions are quite unhandy, and with languages like PHP you can just decode the JSON and treat the content as native arrays and objects. – trincot Jul 17 '16 at 16:59
  • Because who knows based on your comment above – Drew Jul 17 '16 at 17:01
  • Just for testing i tried: JSON_EXTRACT(HistoricalPrices, '$.CreateDate') >NOW() but it doesn't seem to work on multiple json_array() and always returns nothing – Dror Jul 18 '16 at 07:20
  • Still looking for a solve for this issue. – Dror Jul 18 '16 at 07:22

0 Answers0