Nested update query in JSON data type column in mysql 5.7

I have added a new JSON Data type column (bill_plans)in my table . Now I want to update the bill_plans column like this

[{ "cycle": 1, "fee": 1000}, { "cycle": 3, "fee": 2800}, { "cycle": 10, "fee": 10000} ]

I was successful in creating the column and I was able to update the bill_plans columns also.

The table contains bill_cycle and fees also as an existing column so I want to update the bill_plans column something like this

[{ "cycle": value from the bill_cycle column, "fee": value from the fees column}, { "cycle": value from the bill_cycle column, "fee": value from the fees column}]

the simple update query is something like this

update  coaching_class_entries set bill_plans =  ('[{"cycle": 1, "fee": 1000}]') where id = 1;

But now I am not able to understand how to update the bill_plans from the existing columns of the table

Solution

MySQL has predefined functions to perform operations on JSON Arrays and Objects.

You can use the below query to achieve your result.

Method 1 Using general syntax

UPDATE coaching_class_entries     
  SET bill_plans = '[ {"cycle": 1, "fee": 1000 } ]'

In this case you may need to update the values as per the data in columns. You can use CONCAT operator to form the json string

UPDATE coaching_class_entries     
  SET bill_plans = CONCAT('[{"cycle":"', bill_cycle, '","fee":"', fees, '"}]')

Method 2 Using JSON Functions

UPDATE coaching_class_entries     
  SET bill_plans = JSON_ARRAY(JSON_OBJECT("cycle", bill_cycle, "fee", fees))

You can refer to the complete docs here https://dev.mysql.com/doc/refman/5.7/en/json.html