使用 PostgreSQL 12.7，我想從嵌套的 JSON 陣列中獲取產品的最新版本（最大值）。這里是從塔的采樣值fields用于product“AAA”：
"customfield_01":[
{
"id":1303,
"name":"AAA - 1.82.0",
"state":"closed",
"boardId":137,
"endDate":"2021-10-15T10:00:00.000Z",
"startDate":"2021-10-04T01:00:01.495Z",
"completeDate":"2021-10-18T03:02:55.824Z"
},
{
"id":1304,
"name":"AAA - 1.83.0",
"state":"active",
"boardId":137,
"endDate":"2021-10-29T10:00:00.000Z",
"startDate":"2021-10-18T01:00:24.324Z"
}
],
我試過：
SELECT product, jsonb_path_query_array(fields, '$.customfield_01.version') AS version
FROM product.issues;
這是輸出：
| product | version |
|---------------------------------------------------------|
| CCC |[] |
| AAA |["AAA - 1.83.0", "AAA - 1.82.0"] |
| BBB |["BBB - 1.83.0", "BBB - 1.82.0", "BBB - 1.84.0]|
| BBB |["BBB - 1.83.0"] |
| BBB |["BBB - 1.84.0", "BBB - 1.83.0"] |
預期是：
| product | version |
|---------------------------------------------------------|
| AAA |["AAA - 1.83.0" |
| BBB |["BBB - 1.84.0] |
| BBB |["BBB - 1.83.0"] |
| BBB |["BBB - 1.84.0"] |
嘗試 unnest/Array 但它拋出了一個錯誤：
SELECT max(version)
FROM (SELECT UNNEST(ARRAY [jsonb_path_query_array(fields,'$.customfield_01.version')]) AS version FROM product.issues ) AS version;
確實使用了 -1，但它只會獲得最右邊的資料。
jsonb_path_query_array(fields, '$.customfield_01.version') ->> -1
Postgres 和 json 非常新。嘗試閱讀檔案和谷歌，但多次嘗試失敗。
uj5u.com熱心網友回復：
假設product是表的主鍵列。
您可以使用SQL/JSON 路徑運算式：
SELECT product, max(version) AS latest_version
FROM product.issues;
, jsonb_array_elements_text(jsonb_path_query_array(fields, '$.customfield_01.name')) AS version
GROUP BY 1
ORDER BY 1;
但是簡單的jsonb運算子可以實作相同的效果：
SELECT product, max(version ->> 'name') AS latest_version
FROM product.issues
, jsonb_array_elements(fields -> 'customfield_01') AS version
GROUP BY 1
ORDER BY 1;
使用jsonb_array_elements()或jsonb_array_elements_text()取消嵌套jsonb陣列。看：
- 如何將 JSON 陣列轉換為 Postgres 陣列？
unnest()（就像您嘗試過的那樣）可用于取消嵌套Postgres 陣列。
當然，max()只有在“最新版本”按字母順序排列最后時才有效。否則，提取版本部分并將所有部分作為數字處理。喜歡：
SELECT i.product, v.*
FROM issues i
LEFT JOIN LATERAL (
SELECT version ->> 'name' AS version, string_to_array(split_part(version ->> 'name', ' - ', 2), '.')::int[] AS numeric_order
FROM jsonb_array_elements(i.fields -> 'customfield_01') AS version
ORDER BY 2 DESC NULLS LAST
LIMIT 1
) v ON true;
db<>在這里擺弄
看：
- 如何按 XYZ 等典型軟體版本進行訂購？
- 將逗號分隔的列資料拆分為其他列
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/337141.html
標籤：PostgreSQL jsonb json-path-表达式