Filtering JSON with PostgreSQL / Excluding elements of JSON array

473 views Asked by At

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?

0

There are 0 answers