如何從JSONB型別陣列中按值洗掉物件？

我想通過 JSONB 陣列中的唯一“id”值洗掉 JSONB 物件。我不是撰寫 SQL 代碼的專家，但我設法撰寫了連接函式。

例如：從下面的陣列中洗掉此物件。

  {
      "id": "ad26e2be-19fd-4862-8f84-f2f9c87b582e",
      "title": "Wikipedia",
      "links": [
          "https://en.wikipedia.org/1",
          "https://en.wikipedia.org/2"
      ]
    },

架構：

CREATE TABLE users (
    url text not null,
    user_id SERIAL PRIMARY KEY,
    name VARCHAR,
    list_of_links jsonb default '[]'
);

list_of_links格式：

[
  {
      "id": "ad26e2be-19fd-4862-8f84-f2f9c87b582e",
      "title": "Wikipedia",
      "links": [
          "https://en.wikipedia.org/1",
          "https://en.wikipedia.org/2"
      ]
    },
      {
      "id": "451ac172-b93e-4158-8e53-8e9031cfbe72",
      "title": "Russian Wikipedia",
      "links": [
          "https://ru.wikipedia.org/wiki/",
          "https://ru.wikipedia.org/wiki/"
      ]
    },
      {
      "id": "818b99c8-479b-4846-ac15-4b2832ec63b5",
      "title": "German Wikipedia",
      "links": [
          "https://de.wikipedia.org/any",
          "https://de.wikipedia.org/any"
      ]
    },
    ...
]

連接函式：

update users set list_of_links=(
     list_of_links || (select *
         from jsonb_array_elements(list_of_links) 
         where value->>'id'='ad26e2be-19fd-4862-8f84-f2f9c87b582e'
          )
  )
where url='test'
returning *
;

uj5u.com熱心網友回復：

您的json資料是結構化的，因此您必須對其進行解包，對解包后的資料進行操作，然后再次重新打包：

SELECT u.url, u.user_id, u.name, 
       jsonb_agg(
           jsonb_build_object('id', l.id, 'title', l.title, 'links', l.links)
       ) as list_of_links
FROM users u
CROSS JOIN LATERAL jsonb_to_recordset(u.list_of_links) AS l(id uuid, title text, links jsonb)
WHERE l.id != 'ad26e2be-19fd-4862-8f84-f2f9c87b582e'::uuid
GROUP BY 1, 2, 3

該函式jsonb_to_recordset是一個集合回傳函式，因此您必須將其用作行源，并使用該LATERAL子句連接到其原始表，以便該list_of_links列可用于要解包的函式。然后，您可以使用該子句洗掉您不感興趣的記錄WHERE，最后通過將記錄欄位構建到jsonb結構中，然后將各個記錄聚合回陣列來重新打包結構。

uj5u.com熱心網友回復：

我在 JS 上寫了這個，但這與它的作業方式無關。本質上，它從陣列中獲取所有專案，然后找到回傳索引的匹配 id。并使用該索引，我使用“-”運算子獲取索引并將其從陣列中洗掉。對不起，如果我的語法不好。

    //req.body is this JSON object
    //{"url":"test", "id": "ad26e2be-19fd-4862-8f84-f2f9c87b582e"}
    var { url, id } = req.body;
    pgPool.query(
        `
                select list_of_links
                from users
                where url=$1;
        `,
        [url],
        (error, result) => {
            //block code executing further if error is true
            if (error) {
                res.json({ status: "failed" });
                return;
            }
            if (result) {
                // this function just returns the index of the array element where the id matches from request's id
                // 0, 1, 2, 3, 4, 5
                var index_of_the_item = result.rows.list_of_links
                    .map(({ id: db_id }, index) =>
                        db_id === id ? index : false
                    )
                    .filter((x) => x !== false)[0];
                //remove the array element by it's index
                pgPool.query(
                    `
                    update users
                    set list_of_links=(
                       list_of_links - $1::int
                    )
                    where url=$2
                    ;
                   `,
                    [index_of_the_item, url], (e, r) => {...}

                );
            }
        }
    );

標籤：sql PostgreSQL

上一篇：地理資訊系統|ST_Intersects：對混合SRID幾何圖形（Point，0）的操作！=（MultiPolygon，24379）SQL狀態：XX000

下一篇：如何分組然后添加一個值