How to get latest version of a product from a jsonb array?-CodePudding

Using PostgreSQL 12.7, I'd like to get the latest version of a product (maximum value) from a nested JSON array. Here is a sample value from column fields for 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"
      }
   ],

I tried:

SELECT product, jsonb_path_query_array(fields, '$.customfield_01.version') AS version
FROM product.issues;

Here is the output:

| 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"]               |

Expected is:

| product | version                                       |
|---------------------------------------------------------|
| AAA     |["AAA - 1.83.0"                                |
| BBB     |["BBB - 1.84.0]                                |
| BBB     |["BBB - 1.83.0"]                               |
| BBB     |["BBB - 1.84.0"]                               |

Tried unnest/Array but it threw an error:

SELECT max(version)
FROM  (SELECT UNNEST(ARRAY [jsonb_path_query_array(fields,'$.customfield_01.version')]) AS version FROM product.issues ) AS version;

Did using -1, but it will only get the rightmost of the data.

jsonb_path_query_array(fields, '$.customfield_01.version') ->> -1

Very new with Postgres & json. Did try to read documentation and google, however multiple attempts failed.

CodePudding user response：

Assuming product is the primary key column of your table.

You can use a SQL/JSON path expression:

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;

But simple jsonb operators achieve the same:

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;

Use jsonb_array_elements() or jsonb_array_elements_text() to unnest a jsonb array. See:

How to turn JSON array into Postgres array?

unnest() (like you tried) can be used to unnest a Postgres array.

Of course, max() only works while the "latest version" sorts alphabetically last. Else, extract the version part and process all parts as numbers. Like:

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<>fiddle here

See: