I'm currently building a real-time application and using ClickHouseDB as a database. I want to insert manually or update manually the value of a field with data type Array(String).

For example: I have an array like this:

[
  {
    "order": "1",
    "option": "None of the above"
  },
  {
    "order": "2",
    "option": "Environmental Studies"
  },
  {
    "order": "3",
    "option": "STEM"
  }
]

I need to insert it into a column with data type Array(String) in the users table. Is there any way for me to insert manually or update manually?

I've tried the SQL code as follows:

ALTER TABLE users UPDATE preferred_areas = [{ "order": "1", "option": "None of the above" }, { "order": "2", "option": "Environmental Studies" }, { "order": "3", "option": "STEM" }] WHERE username = 'changpham'

But I've encountered the error mentioned in the attached image. Do you guys have no idea about it? Do you know how to fix it? Please help me to do that. Thank you so much.

I'm currently building a real-time application and using ClickHouseDB as a database. I want to insert manually or update manually the value of a field with data type Array(String).

For example: I have an array like this:

[
  {
    "order": "1",
    "option": "None of the above"
  },
  {
    "order": "2",
    "option": "Environmental Studies"
  },
  {
    "order": "3",
    "option": "STEM"
  }
]

I need to insert it into a column with data type Array(String) in the users table. Is there any way for me to insert manually or update manually?

I've tried the SQL code as follows:

ALTER TABLE users UPDATE preferred_areas = [{ "order": "1", "option": "None of the above" }, { "order": "2", "option": "Environmental Studies" }, { "order": "3", "option": "STEM" }] WHERE username = 'changpham'

But I've encountered the error mentioned in the attached image. Do you guys have no idea about it? Do you know how to fix it? Please help me to do that. Thank you so much.

• sql
• clickhouse
• clickhouse-client

• Please show the error as text not as an image – Dale K Commented Mar 6 at 7:58

1 Answer 1

[{ "order": "1", "option": "None of the above" }, { "order": "2", "option": "Environmental Studies" }, { "order": "3", "option": "STEM" }] thi is not Array(String)

maybe better try

ALTER TABLE users UPDATE preferred_areas = ['{ "order": "1", "option": "None of the above" }', '{ "order": "2", "option": "Environmental Studies" }', '{ "order": "3", "option": "STEM" }'] 
WHERE username = 'changpham'

but please avoid too many mutations this is heavy operation which will rewrite whole data part, instead of one row

better one mutation with WHERE username IN (...) instead of multiple mutations with WHERE username=X