How to use PSQL to extract data from an object (inside an array inside an object inside an array)

Question

This is data that is currently sitting in a single cell (e.g. inside warehouse table in warehouse_data column) in our database (I'm unable to change the structure/DB design so would need to work with this), how would I be able to select the name of the shirt with the largest width? In this case, would expect output to be tshirt_b (without quotation marks)

{
    "wardrobe": {
        "apparel": {
            "variety": [
                {
                    "data": {
                        "shirt": {
                            "size": {
                                "width": 30
                            }
                        }
                    },
                    "names": [
                        {
                            "name": "tshirt_a"
                        }
                    ]
                },
                {
                    "data": {
                        "shirt": {
                            "size": {
                                "width": 40
                            }
                        }
                    },
                    "names": [
                        {
                            "name": "tshirt_b"
                        }
                    ]
                }
            ]
        }
    }
}

I've tried a select statement, being able to get out

"names": [
             {
                "name": "tshirt_b"
             }
         ]

but not too much further than that e.g.:

select jsonb_array_elements(warehouse_data#>'{wardrobe,apparel,variety}')->>'names' 
from 'warehouse' 
where id = 1;

In this table, we'd have 2 columns, one with the data and one with a unique identifier. I imagine I'd need to be able to select into size->>width, order DESC and limit 1 (if that's able to then limit it to include the entire object with data & shirt or with the max() func?

I'm really stuck so any help would be appreciated, thank you!

Answer 1

You'll first want to normalise the data into a relational structure:

SELECT
  (obj #>> '{data,shirt,size,width}')::int AS width,
  (obj #>> '{names,0,name}') AS name
FROM warehouse, jsonb_array_elements(warehouse_data#>'{wardrobe,apparel,variety}') obj
WHERE id = 1;

Then you can do your processing on that as a subquery, e.g.

SELECT name
FROM (
  SELECT
    (obj #>> '{data,shirt,size,width}')::int AS width,
    (obj #>> '{names,0,name}') AS name
  FROM warehouse, jsonb_array_elements(warehouse_data#>'{wardrobe,apparel,variety}') obj
  WHERE id = 1
) shirts
ORDER BY width DESC
LIMIT 1;