Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise

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?

share|improve this question
2  
schinckel.net/2014/05/25/querying-json-in-postgres Check this out – FirebladeDan Jun 18 '15 at 21:34
    
The JSON you've provided in this question is invalid. – Elliot B. Jun 18 '15 at 21:36
    
Sorry, left out one line on both. They are both valid now. – NateW Jun 18 '15 at 22:27
    
@FirebladDan the link was interesting but relied on Django server code to filter what I needed. Thanks though. – NateW Jun 18 '15 at 22:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.