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

Trying to build a filter system where I can dynamically pass multiple ID and value and get the entire record from this [{id: 1, value: 'Yes'},{id: 2, value: 'No'}] — Tristen Russ
– Tristen Russ, Commented Dec 6, 2021 at 0:08

Bergi · Accepted Answer · 2021-12-06 01:42:18Z

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

edited Dec 6, 2021 at 1:42

answered Dec 6, 2021 at 0:24

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

6 Comments

Tristen Russ Over a year ago

Getting a 'ERROR: cannot extract elements from a scalar' when setting ANY jsonb_array_elements_text(value). What would the main reason to use this over the

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

query?

Bergi Over a year ago

Oh, I see now that even for item 3 the value can be a string and is not always an array - I would have expected the data type to depend on the question you're asking. The benefit would be clearer semantics, "is exactly an element of the array" is a different query than "the string representation of the array somehow contains the substring somewhere".

Bergi Over a year ago

Yeah jsonb_array_elements_text is not gonna work with that data (unless using very complicated CASE jsonb_type(value) OF … expressions). Much better alternative: jsonpath!

Tristen Russ Over a year ago

I see your edit! This is awesome! I hadn't looked into jsonpath but this seems exactly what it was built for!

Bergi Over a year ago

Looks fine to me, but I don't have any experience with jsonpath performance either. I guess you could do something like questionnaire_response @? '$[*]?(@.id == "3" && @.value[*] == "Red" && @.value[*] == "Black")' to shorten it.

|

Collectives™ on Stack Overflow

Query a JSONB object array

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related