Handling array structures in PostgreSQL JSON objects (FOLIO-921)

[FOLIO-923] Sorting on repeated fields in PostgreSQL JSON records Created: 03/Nov/17  Updated: 24/May/19

Status: Open
Project: FOLIO
Components: None
Affects versions: None
Fix versions: None
Parent: Handling array structures in PostgreSQL JSON objects

Type: Sub-task Priority: P3
Reporter: Niels Erik Nielsen Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original estimate: Not Specified

Issue links:
Relates
relates to RMB-382 Sort by array-of-strings and array-of... Open
Sprint:
Development Team: Core: Platform

 Description   

This is more of a logical problem than a technical one I guess.

When we have repeated 'creators' or 'identifiers' it doesn't make a lot of sense to try very hard to sort records on all of the elements.

Do we sort records on the first author in the array?



 Comments   
Comment by Jakub Skoczen [ 07/Nov/17 ]

Julian Ladisch shale99 How does the sort on array values work right now?

Comment by Niels Erik Nielsen [ 07/Nov/17 ]

select jsonb->'creators' from diku_mod_inventory_storage.instance order by jsonb->'creators'>0>'name';

The current request:

http://localhost:9130/instance-storage/instances?limit=30&query=cql.allRecords%3D1%20sortby%20creators

"creators" : [

{ "name" : "Fielding, Helen", "creatorTypeId" : "2b94c631-fca9-a892-c730-03ee529ffe2a" }

]

Comment by Jakub Skoczen [ 07/Nov/17 ]

I would expect sort to be expressed in an explicit way like so:

... sortby creators[0].name

Comment by Julian Ladisch [ 07/Nov/17 ]

The current implementation uses postgres' array sort that first sorts by number of array elements and then on the first array element, then on the second array element, etc.: http://sqlfiddle.com/#!17/ebbb7/1
[]
["a"]
["b"]
["c", "c"]
["c", "d"]
["ca", "c"]
["ca", "d"]
["b", "b", "b"]
["b", "b", "c"]
["a", "a", "a", "a"]
["a", "b", "c", "d"]

Generated at Thu Feb 08 23:09:19 UTC 2024 using Jira 1001.0.0-SNAPSHOT#100246-sha1:7a5c50119eb0633d306e14180817ddef5e80c75d.