Nested update query in JSON data type column in mysql 5.7

Question

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

Ram Babu · Accepted Answer · 2018-03-12 08:23:05Z

3

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

edited Mar 12, 2018 at 8:23

answered Mar 12, 2018 at 8:10

Ram Babu

2,9323 gold badges27 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

Nested update query in JSON data type column in mysql 5.7

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