update column value with a json

Question

If I try this with a numbers col:

UPDATE TEST_TABLE SET META_ROW_NUM = 2

it works. However, when I try to update another field with a JSON (also tried without the quotation marks ""):

UPDATE TEST_TABLE 
SET JSON_DATA = "{
  "Business_Type": "载货",
  "Collected_Article_Quantity": null,
  "Consignee_Company_ContactPerson": null,
  "Consignee_Company_Email": null,
}"

I get syntax errors like this:

SQL Error [1003] [42000]: SQL compilation error:
syntax error line 3 at position 1 unexpected 'Business_Type'.

The TYPEOF(JSON_DATA) is Object. When I hover over the col in Dbeaver, I see that the type is Variant:

The table looks like this:

Answer 1

It seems like you might want to use OBJECT_CONSTRUCT to build you values, it has a nice side effect, that properties with a NULL value are not inserted

thus:

SELECT column1 as id,
    OBJECT_construct(column2, column3, column4, column5, column6, column7, column8, column9) as json
FROM VALUES
    (1, 'Type', 'xxi', 'Quantity', null, 'ContactPerson', null, 'Email', null),
    (2, 'Type', 'xxi', 'Quantity', 'many', 'ContactPerson', null, 'Email', null),
    (3, 'Type', 'xxi', 'Quantity', 'many', 'ContactPerson', 'Simeon', 'Email', null),
    (4, 'Type', 'xxi', 'Quantity', 'many', 'ContactPerson', 'Simeon', 'Email', 'example@example.com')
;

gives:

ID	JSON
1	{ "Type": "xxi" }
2	{ "Quantity": "many", "Type": "xxi" }
3	{ "ContactPerson": "Simeon", "Quantity": "many", "Type": "xxi" }
4	{ "ContactPerson": "Simeon", "Email": "example@example.com", "Quantity": "many", "Type": "xxi" }

which makes "more" sense to set the JSON to values from the related data that is used to decide what to update.

but if you data you are build via is spare OBJECT_AGG works super nice:

    ,OBJECT_AGG(column2, column3) as json
FROM VALUES
    (1, 'Type', 'xxi'),
    (1, 'Quantity', null),
    (1, 'ContactPerson', null),
    (1,  'Email', null),
    (2, 'Type', 'xxi'),
    (2, 'Quantity', 'many'),
    (2, 'ContactPerson', null),
    (2, 'Email', null),
    (3, 'Type', 'xxi', l),
    (3, 'Quantity', 'many'),
    (3, 'ContactPerson', 'Simeon'),
    (3, 'Email', null),
    (4, 'Type', 'xxi'),
    (4, 'Quantity', 'many'),
    (4, 'ContactPerson', 'Simeon'),
    (4, 'Email', 'example@example.com')
GROUP BY 1
;

gives:

ID	JSON
1	{ "Type": "xxi" }
2	{ "Quantity": "many", "Type": "xxi" }
3	{ "ContactPerson": "Simeon", "Quantity": "many", "Type": "xxi" }
4	{ "ContactPerson": "Simeon", "Email": "example@example.com", "Quantity": "many", "Type": "xxi" }

Answer 2

Using ' and PARSE_JSON/TRY_PARSE_JSON:

UPDATE TEST_TABLE 
SET JSON_DATA = PARSE_JSON('{
  "Business_Type": "载货",
  "Collected_Article_Quantity": null,
  "Consignee_Company_ContactPerson": null,
  "Consignee_Company_Email": null
}');

Full demo:

CREATE OR REPLACE TABLE t(JSON_DATA VARIANT);
INSERT INTO t VALUES(NULL);

UPDATE t
SET JSON_DATA = PARSE_JSON('{
  "Business_Type": "载货",
  "Collected_Article_Quantity": null,
  "Consignee_Company_ContactPerson": null,
  "Consignee_Company_Email": null
}');

SELECT * FROM t;

Output: