Fetching array data from jsonb postgresql

Question

I am having a hard time in fetching values from jsonb where array is given.

{
    "question1": [
        "A",
        "B",
        "C"
    ],
    "question2": [
        "D",
        "E",
        "F"
    ],
    "question3": "G",
}

for question3 I can fetch value as column_name ->> 'question3'::text and I have the value as "G" but in the case of question1 and question2 I want to fetch value as an array and check if a particular element exists in question1 array or question2 array.

Do you want to return the complete column content, but only for rows where the value exists in the array? Or do you just want to return the array that contains the element (i.e. just a part of the complete JSON). — user330315
– user330315, Commented Oct 28, 2020 at 7:11

user330315 · Accepted Answer · 2020-10-28 07:13:48Z

1

If you just want to filter the rows where a specific value exists in the JSON array, you can use the contains operator @>:

select *
from the_table
where the_column -> 'question1' @> '["A"]'
   or the_column -> 'question2' @> '["A"]'

answered Oct 28, 2020 at 7:13

user330315

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

Comments

Sharif · Accepted Answer · 2020-10-28 07:03:28Z

1

Use the postgres json function jsonb_array_elements / json_array_elements (link here) will get you the result. jsonb_array_elements(column_name -> 'question1')

answered Oct 28, 2020 at 7:03

Sharif

3223 silver badges13 bronze badges

Comments

Shivam Arora · Accepted Answer · 2020-10-28 07:37:27Z

1

You can check type of the fetched json value the take action according to that: If it is string then simply you can print that or if it is array then you can proceed further and search for an element in the json array.

 with tempJ(json_col) as (values('{"question1":[ "A", "B", "C" ], "question2":[ "D", "E", "F" ], "question3":"G"}'::jsonb))
  SELECT case jsonb_typeof(json_col->'question2')
              when 'array' then (json_col->'question2')
              else (json_col->'question2') end
  from tempJ

To search element from the array you can use:

select '[ "A", "B", "C" ]'::jsonb @> '"A"'

answered Oct 28, 2020 at 7:37

Shivam Arora

3642 silver badges8 bronze badges

Collectives™ on Stack Overflow

Fetching array data from jsonb postgresql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related