How to insert a new key-value into the first row of a table containing a json (Snowflake)

Question

I have a table "MY_TABLE" with one column "VALUE" and the first row of the column contains a json that looks like:

{
  "VALUE": {
    "c1": "name",
    "c10": "age",
    "c100": "gender",
    "c101": "address",
    "c102": "status"
}
}

I would like to add a new key-value pair to this json in the first row where the pair is "c125" : "job" so that the result looks like:

{
  "VALUE": {
    "c1": "name",
    "c10": "age",
    "c100": "gender",
    "c101": "address",
    "c102": "status",
    "c125": "job"
}
}

I tried:

SELECT object_insert(OBJECT_CONSTRUCT(*),'c125', 'job') FROM MY_TABLE;

But it inserted the new key value pair into the wrong spot so the result looks like:


{
  "VALUE": {
    "c1": "name",
    "c10": "age",
    "c100": "gender",
    "c101": "address",
    "c102": "status"
},
  "c125": "job"
}

Is there another way to do this? Thanks!

user_13915313 · Answer

Another, similar approach, using OBJECT_INSERT -

For original table (assuming, column data-type is variant, else use parse_json function) -

select * from temp_1;
+------------------------+
| COL1                   |
|------------------------|
| {                      |
|   "VALUE": {           |
|     "c1": "name",      |
|     "c10": "age",      |
|     "c100": "gender",  |
|     "c101": "address", |
|     "c102": "status"   |
|   }                    |
| }                      |
+------------------------+

Query with added key ("c31":101) as output -

select 
object_insert(col1,'VALUE',object_insert(col1:VALUE,'c31',101),TRUE) 
as output_col from temp_1;

+------------------------+
| OUTPUT_COL             |
|------------------------|
| {                      |
|   "VALUE": {           |
|     "c1": "name",      |
|     "c10": "age",      |
|     "c100": "gender",  |
|     "c101": "address", |
|     "c102": "status",  |
|     "c31": 101         |
|   }                    |
| }                      |
+------------------------+

Clause used in a update (can be predicated based on another column to be used a key) -

update temp_1 set col1 = object_insert(col1,'VALUE',object_insert(col1:VALUE,'c31',101),TRUE);

After update -

select * from temp_1;

+------------------------+
| COL1                   |
|------------------------|
| {                      |
|   "VALUE": {           |
|     "c1": "name",      |
|     "c10": "age",      |
|     "c100": "gender",  |
|     "c101": "address", |
|     "c102": "status",  |
|     "c31": 101         |
|   }                    |
| }                      |
+------------------------+

user_5070879 · Answer

One approach could be flattening the result first and construct again:

CREATE TABLE MY_TABLE
AS
SELECT PARSE_JSON('{
  "VALUE": {
    "c1": "name",
    "c10": "age",
    "c100": "gender",
    "c101": "address",
    "c102": "status"
}
}') AS VALUE;

SELECT *  FROM MY_TABLE;

Before:

Query:

WITH cte(key, value) AS (
    SELECT 'c125', 'job'::VARIANT
    UNION ALL
    SELECT s.key, s.value
    FROM MY_TABLE
    ,TABLE(FLATTEN (input => VALUE, path => 'VALUE')) s
)
SELECT OBJECT_CONSTRUCT('VALUE', OBJECT_AGG(key, value))
FROM cte;

Output:

How to insert a new key-value into the first row of a table containing a json (Snowflake)

2 Answers