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!

What if there are zero or more than one objects in the `names` array, whichname do you wan to get? — Bergi, Feb 10 '23 at 01:00
Ahh, so if in the case of 0 objects in names array, then I don't think it'd get populated at all in the database (ie the entire object including names + data won't exist if there's no tshirt_b in the first place). similarly with more than 1 - I believe it'll only ever return 1 inside the names object, if it had a new name e.g. tshirt_c, a new object in "variety" could be created — MB360, Feb 10 '23 at 01:10

score 1 · Accepted Answer · answered Feb 10 '23 at 01:06

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;

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

1 Answers1