*** This version of Confluence is for testing only and contains a copy of content from June 29th 2026. No changes will be preserved. ***
...
ORDER BY name, jsonb_extract_path_text (values.jsonb,'id'), jsonb_extract_path_text (values.jsonb,'value')
...
4. Extracting Arrays - Alternative to using cross joins
Cross join queries will return only those records that have all the array elements you are trying to find. (Note that it is possible to use multiple cross joins in the FROM stanza - but the results will show only those records that have ALL the array elements specified in the cross joins. If a record is missing even a single specified element, it will drop out.) Because of this, you either need to do separate cross-join queries for each needed element, and left-join them together to a list of all records; or else use this method that employs nested json extract statements.
...