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:

CodePudding user response：

Using ' and

CodePudding user response：

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', '[email protected]')
;

gives:

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', '[email protected]')
GROUP BY 1
;

gives: