How do I query a string from JSON based on another string within the JSON in PostgreSQL?

Question

MyTable.MyField in my PostgreSQL database contains the following (simplified) JSON block:

{
    "base": {
        "fields": [
            {
                "fieldid": "c12f",
                "fieldname": "sizes",
                "choices": [
                    {
                        "choiceid": "2db3",
                        "size": "small"
                    },
                    {
                        "choiceid": "241f",
                        "size": "medium"
                    },
                    {
                        "choiceid": "3f52",
                        "size": "large"
                    }
                ],
                "answer": "241f"
            }
        ]
    }
}

How can I use the value of answer to extract the chosen size from the choices array please (i.e. in this case "medium")?

(Note: I have tried. For a TLDR version of this question see Trying to construct PostgreSQL Query to extract from JSON a text value in an object, in an array, in an object, in an array, in an object .)

Thank you.

Bergi · Accepted Answer · 2019-11-28 09:23:16Z

2

You can use json_array_elements in a lateral join, then just query the fields that you are looking for:

SELECT
  field -> 'fieldid' AS id,
  choice -> 'size' AS size
FROM
  my_table,
  json_array_elements(json_column -> 'base' -> 'fields') field,
  json_array_elements(field -> 'choices') choice
WHERE
  field ->> 'answer' = choice ->> 'choiceid'

edited Nov 28, 2019 at 9:23

answered Nov 27, 2019 at 22:53

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Wayne Ivory Over a year ago

Thanks for your answer. I considered editing it because I think I picked up a few typos but as I'm a bit of a novice in PostgreSQL and JSON I thought it better to point them out and let you (or somebody else) edit if appropriate. The first one is easy - a missing comma after the first SELECTed field; secondly you said to do a lateral join yet you didn't use the LATERAL keyword; thirdly I got an operator error on the final "=" character - is that because the final -> operators should be ->> operators?

Bergi Over a year ago

@WayneIvory Thanks for the info! LATERAL is optional on set-returning functions iirc. And yes, json doesn't support equality, only jsonb does - casting to a string is easiest here indeed.

Collectives™ on Stack Overflow

How do I query a string from JSON based on another string within the JSON in PostgreSQL?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related