I want to be able to return part of a JSON using PostgreSQL. For example if I have the JSON
{
"dress_code": "casual",
"design": "Solid",
"fit": "Straight-cut",
"aesthetic": [
{
"aesthetic_id": 1,
"primary_color": "light blue",
"secondary_color": "light gray",
"sizes": [
{
"size_id": "1",
"basic_size": "S",
"waist": 30,
"pictures": [
{
"angle": "front",
"url": "fashion.com/img1f"
},
{
"angle": "back",
"url": "fashion.com/img1b"
}
]
},
{
"size_id": "2",
"basic_size": "M",
"waist": 33,
"pictures": [
{
"angle": "front",
"url": "fashion.com/img1f"
},
{
"angle": "back",
"url": "fashion.com/img1b"
}
]
}
]
},
{
"aesthetic_id": 2,
"primary_color": "dark blue",
"secondary_color": "light gray",
"sizes": [
{
"size_id": "3",
"basic_size": "S",
"waist": 30,
"pictures": [
{
"angle": "front",
"url": "fashion.com/img2f"
},
{
"angle": "back",
"url": "fashion.com/img2b"
}
]
},
{
"size_id": "4",
"basic_size": "M",
"waist": 33,
"pictures": [
{
"angle": "front",
"url": "fashion.com/img2f"
},
{
"angle": "back",
"url": "fashion.com/img2b"
}
]
}
]
}
]
}
And, for example, I only want the array and information of aesthetic_id = 2 and size_id = 4, which would be:
{
"dress_code": "casual",
"design": "Solid",
"fit": "Straight-cut",
"aesthetic": [
{
"aesthetic_id": 2,
"primary_color": "dark blue",
"secondary_color": "light gray",
"sizes": [
{
"size_id": "4",
"basic_size": "M",
"waist": 33,
"pictures": [
{
"angle": "front",
"url": "fashion.com/img2f"
},
{
"angle": "back",
"url": "fashion.com/img2b"
}
]
}
]
}
]
}
Is there a good way about filtering JSON data like this using PostgreSQL?