如何从jsonb数组获得产品的最新版本?

Question

使用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。尝试阅读文档和谷歌，但多次尝试失败。

Answer 1

假设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<此处小提琴>

:

• 如何按X.Y.Z等典型软件发布版本进行订购?
• 将逗号分隔的列数据拆分为其他列