Handling array structures in PostgreSQL JSON objects
(FOLIO-921)
|
|
| 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: |
|
||||||||
| 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' The current request: "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 |