Обновить смешанный и вложенный объект в Snowflake

У меня есть таблица Snowflake с одним столбцом вариантов (raw).

Каждая строка в этой таблице является сложной (как словари, так и массивы) и вложенной (несколько иерархий).

Я хочу иметь возможность обновлять определенный элемент в некотором массиве.

Будет легче понять это на примере, поэтому рассматривайте это как строку в таблице:

{
  "id": "1234"
  "x_id": [
    {
      "y_id": "790437306684007491",
      "y_state": "some_state"
    }
  ],
  "comments": {
    "1": [
      {
        "comment_id": "bb288743-3b73-4423-b76b-f26b8c37f7d4",
        "comment_timestamp": "2021-02-10 14:53:25.667564",
        "comment_text": "Hey"
      },
      {
        "comment_id": "7378f332-93c4-4522-9f73-3b6a8a9425ce",
        "comment_text": "You",
        "comment_timestamp": "2021-02-10 14:54:21.337046"
      }
    ],
    "2": [
      {
        "comment_id": "9dd0cbb0-df80-4b0f-b399-9ee153161462",
        "comment_text": "Hello",
        "comment_timestamp": "2021-02-09 09:26:17.987386"
      },
      {
        "comment_id": "1a3bf1e8-82b5-4a9c-a959-a1da806ce7e3",
        "comment_text": "World",
        "comment_timestamp": "2021-02-09 09:28:32.144175"
      }
    ]
  }
}

И я хочу обновить текст комментария определенного комментария .

Я знаю, что могу обновить весь JSON программно и обновить весь объект с помощью PARSE_JSON, но этого подхода недостаточно, потому что могут быть другие обновления, которые переопределят другие комментарии, поэтому этот подход не удастся (поскольку эти обновления будут перекрывать друг друга).

Итак, сначала я попробовал наивный подход (который, как я знал, не сработает, но мне пришлось попробовать):

update table1
set raw['comments'][Ƈ'][0]["comment_text"] = 'please work'

И неудивительно, что я получаю следующую ошибку:

SQL compilation error: syntax error line 2 at position 7 unexpected '['.

Затем я попробовал OBJECT_INSERT, который должен позволять обновлять объект, но это не удается из-за вложенного ключа (Ƈ'):

UPDATE table1
SET raw = OBJECT_INSERT(raw:comments:1, "comment_test", 'please work') 

с ошибкой

SQL compilation error: syntax error line 1 at position 99 unexpected Ƈ'.

(Я также пробовал несколько вариантов этого подхода с raw:comments:"1", raw:comments:1[0] или raw['comments'][Ƈ'] и некоторыми другими)

Я также попытался реорганизовать объект, чтобы вместо комментариев в виде словаря объединить комментарии в массив, например:

{
  "id": "1234"
  "x_id": [
    {
      "y_id": "790437306684007491",
      "y_state": "some_state"
    }
  ],
  "comments": [
      {
        "comment_id": "bb288743-3b73-4423-b76b-f26b8c37f7d4",
        "comment_timestamp": "2021-02-10 14:53:25.667564",
        "comment_text": "Hey"
        "comment_key": "1"
      },
      {
        "comment_id": "7378f332-93c4-4522-9f73-3b6a8a9425ce",
        "comment_text": "You",
        "comment_timestamp": "2021-02-10 14:54:21.337046"
        "comment_key": "1"
      }
      {
        "comment_id": "9dd0cbb0-df80-4b0f-b399-9ee153161462",
        "comment_text": "Hello",
        "comment_timestamp": "2021-02-09 09:26:17.987386",
        "comment_key": "2"
      },
      {
        "comment_id": "1a3bf1e8-82b5-4a9c-a959-a1da806ce7e3",
        "comment_text": "World",
        "comment_timestamp": "2021-02-09 09:28:32.144175",
        "comment_key": "2"
      }
    ]
}

Но это не приближает меня к решению. Я искал какую-то функцию ARRAY_REPLACE, которая заменяет элемент в массиве, но не похоже, что такая функция существует (all semi-structured related functions)

Я также рассматривал возможность использования JavaScript UDF для этого, но я не нашел никакого источника UDF, который действительно может обновлять строку (все они используются для получения данных, а не для их обновления, насколько я знаю видел).

Есть ли способ добиться того, чего я хочу?

Большое спасибо!

Всего 2 ответа


Вы можете обновлять сложные структуры JSON с помощью пользовательских функций JavaScript. Вот образец. Обратите внимание, что оба ваших образца JSON содержат ошибки. Я использовал второй и исправил пропущенные запятые.

-- Create a temp table with a sigle variant. By convention, I uses "v" as the name of any
-- column in a single-column table. You can change to "raw" in your code.
create or replace temp table foo(v variant);

-- Create a UDF that updates the exact key you want to update.
-- Unfortunately, JavaScript treats the object path as a constant so you can't make this 
-- a string that you pass in dynamically. There are ways around this possibly, but 
-- library restrictions would require a raw JavaScript parser function. Just update the
-- path you need in the UDF.
create or replace function update_json("v" variant, "newValue" string)
returns variant
language javascript
as
$$
   v.comments[0].comment_text = newValue;
   return v;
$$;

-- Insert the corrected JSON into the variant field
insert into foo select parse_json('{
    "id": "1234",
    "x_id": [{
        "y_id": "790437306684007491",
        "y_state": "some_state"
    }],
    "comments": [{
            "comment_id": "bb288743-3b73-4423-b76b-f26b8c37f7d4",
            "comment_timestamp": "2021-02-10 14:53:25.667564",
            "comment_text": "Hey",
            "comment_key": "1"
        },
        {
            "comment_id": "7378f332-93c4-4522-9f73-3b6a8a9425ce",
            "comment_text": "You",
            "comment_timestamp": "2021-02-10 14:54:21.337046",
            "comment_key": "1"
        },
        {
            "comment_id": "9dd0cbb0-df80-4b0f-b399-9ee153161462",
            "comment_text": "Hello",
            "comment_timestamp": "2021-02-09 09:26:17.987386",
            "comment_key": "2"
        },
        {
            "comment_id": "1a3bf1e8-82b5-4a9c-a959-a1da806ce7e3",
            "comment_text": "World",
            "comment_timestamp": "2021-02-09 09:28:32.144175",
            "comment_key": "2"
        }
    ]
}');

-- Show how the change works without updating the row
select update_json(v, 'please work') from foo;

-- Now update the row using the output. Note that this is updating the 
-- whole variant field, not a portion of it.
update foo set v = update_json(v, 'please work');

-- Show the updated key
select v:comments[0].comment_text::string from foo;

Наконец, если вы хотите изменить свойство, для которого вам нужно просмотреть ключи, чтобы сначала найти то, что вам нужно, вы можете сделать это в цикле в JavaScript. Например, если вам нужен не первый комментарий, а тот, у которого есть конкретный UUID или comment_text и т. Д., Вы можете перебрать его, чтобы найти его и обновить comment_key на той же итерации цикла.


Спасибо, работает!

Мне удалось заставить его работать с помощью встроенных функций -

Предполагая, что нам известна позиция комментария (в этом примере позиция = 3):

UPDATE table1 SET 
raw = object_construct(
  'id', raw:id,
  'x_id', raw:x_id,
  'comments', array_cat(array_append(array_slice(raw:comments ,0 ,2), parse_json('{"id": "3", "comment_text": "please work"}')) , ARRAY_SLICE(raw:comments,3,array_size(raw:comments)))
)
WHERE raw['id'] = 'some_id' 

Но я все еще думаю, какой подход будет работать лучше.

В любом случае спасибо, очень помогло.


Есть идеи?

10000