如何在jsonb列中动态更新数组中的所有值？

Question

我正在节点中使用knex。PostgreSQL数据库上的js项目。将使用knex.schema。raw（原始SQL）来实现这一点。

我需要更新jsonb列中json对象的“grades”数组中等级名称（“1st”等）的所有实例。“grades”是整个json对象中的一个值，这是一个教训。

数组当前包含类似于“grades”的内容：[“Pre-K”，“K”，“4th”，“5th”，“6th”，“7th”，“8th”]，它需要变成“grades”：[“PK”，“K”，“4”，“5”，“6”，“7”，“8”]。

基本上我需要

[“Pre-K”、“K”、“1”、“2”、“3”、“4”、“5”、“6”、“7”、“8”、“9”、“10”、“11”、“12”]

要成为

[“PK”，“K”，“1”，“2”，“3”，“4”，“5”，“6”，“7”，“8”，“9”，“10”，“11”，“12”]

有没有办法检查jsonb数组的内容，获取等级的索引，并用所需的等级更新该索引？还是另一种在jsonb类型列中转换数组数据的方法？

（我最初的想法是创建一个["Pre-K"，"K"，"1"，2"，"3"，"4"，"5"，"6"，"7"，"8"，"9"，"10"，"11"，"12"]组合的每个排列列表，然后从存在的数据中替换出所需的数组，但这似乎有点笨拙，尽管在技术上是可能的。不过，有了这个，我仍然需要在jsonb cell中找到数组中项目的索引。）

Answer 1

使用正则表达式消除冗余子字符串。

update my_table
set json_col = jsonb_set(
        json_col, 
        '{grades}', 
        (
            select jsonb_agg(regexp_replace(value, 're-|st|nd|rd|th', ''))
            from jsonb_array_elements_text(json_col->'grades')
        )
    );

db

一个更简单、更快的版本（尽管可能会被认为有点杂乱无章）：

update my_table
set json_col = jsonb_set(
        json_col,
        '{grades}',
        regexp_replace(json_col->>'grades', 're-|st|nd|rd|th', '', 'g')::jsonb
    )

阅读有关POSIX正则表达式的更多信息。