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

CodePudding user response：

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)}