Query a JSONB object array

Question

I did a DB Fiddle of what the table is kinda looking like https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/3382

Data in the table looks like this

[
    {
        "id": 1,
        "form_id": 1,
        "questionnaire_response": [
            {
                "id": "1",
                "title": "Are you alive?",
                "value": "Yes",
                "form_id": 0,
                "shortTitle": "",
                "description": ""
            },
            {
                "id": "2",
                "title": "Did you sleep good?",
                "value": "No",
                "form_id": 0,
                "shortTitle": "",
                "description": ""
            },
            {
                "id": "3",
                "title": "Whats favorite color(s)?",
                "value": [
                    "Red",
                    "Blue"
                ],
                "form_id": 0,
                "shortTitle": "",
                "description": ""
            }
        ]
    },
    {
        "id": 2,
        "form_id": 1,
        "questionnaire_response": [
            {
                "id": "1",
                "title": "Are you alive?",
                "value": "Yes",
                "form_id": 0,
                "shortTitle": "",
                "description": ""
            },
            {
                "id": "2",
                "title": "Did you sleep good?",
                "value": "Yes",
                "form_id": 0,
                "shortTitle": "",
                "description": ""
            },
            {
                "id": "3",
                "title": "Whats favorite color(s)?",
                "value": "Black",
                "form_id": 0,
                "shortTitle": "",
                "description": ""
            }
        ]
    },
    {
        "id": 3,
        "form_id": 1,
        "questionnaire_response": [
            {
                "id": "1",
                "title": "Are you alive?",
                "value": "Yes",
                "form_id": 0,
                "shortTitle": "",
                "description": ""
            },
            {
                "id": "2",
                "title": "Did you sleep good?",
                "value": "No",
                "form_id": 0,
                "shortTitle": "",
                "description": ""
            },
            {
                "id": "3",
                "title": "Whats favorite color(s)?",
                "value": [
                    "Black",
                    "Red"
                ],
                "form_id": 0,
                "shortTitle": "",
                "description": ""
            }
        ]
    }
]

I have a query select * from form_responses,jsonb_to_recordset(form_responses.questionnaire_response) as items(value text, id text) where (items.id = '3' AND items.value like '%Black%');

But unable to do more than one object like select * from form_responses,jsonb_to_recordset(form_responses.questionnaire_response) as items(value text, id text) where (items.id = '3' AND items.value like '%Black%') AND (items.id = '2' AND items.value like '%Yes%');

The value field in the object could be an array or a single value also.. unpredictable.. I feel like I'm close but also not sure if im using the correct query in the first place.

Any help would be appreciated!

EDIT

select * from form_responses where(
  questionnaire_response @> '[{"id": "2", "value":"No"},{"id": "3", "value":["Red"]}]')

Seems to work but not sure if this is the best way to do it

Answer 1

Your current query returns one result row per item. None of these rows has both id = 3 and id = 2. If your goal is to select the entire form response, you need to use a subquery (or rather, two of them):

SELECT *
FROM form_responses
WHERE EXISTS(
    SELECT *
    FROM jsonb_to_recordset(form_responses.questionnaire_response) as items(value text, id text)
    WHERE items.id = '3'
      AND items.value like '%Black%'
  )
  AND EXISTS(
    SELECT *
    FROM jsonb_to_recordset(form_responses.questionnaire_response) as items(value text, id text)
    WHERE items.id = '2'
      AND items.value like '%Yes%'
  );

or alternatively

SELECT *
FROM form_responses
WHERE (
    SELECT value
    FROM jsonb_to_recordset(form_responses.questionnaire_response) as items(value text, id text)
    WHERE items.id = '3'
  ) like '%Black%'
  AND (
    SELECT value
    FROM jsonb_to_recordset(form_responses.questionnaire_response) as items(value text, id text)
    WHERE items.id = '2'
  ) like '%Yes%';

A nicer alternative would be using json path queries:

SELECT *
FROM form_responses
WHERE questionnaire_response @@ '$[*]?(@.id == "1").value == "Yes"'
  AND questionnaire_response @@ '$[*]?(@.id == "3").value[*] == "Black"'
-- in one:
SELECT *
FROM form_responses
WHERE questionnaire_response @@ '$[*]?(@.id == "1").value == "Yes" && $[*]?(@.id == "3").value[*] == "Black"'

The [*] even has the correct semantics for that sometimes-string-sometimes-array value. And if you know the indices of the items with those ids, you can even simplify to

SELECT *
FROM form_responses
WHERE questionnaire_response @@ '$[0].value == "Yes" && $[2].value[*] == "Black"'

^{(dbfiddle demo)}