CODEWITHSUNDEEP
postgresql
smith
smith

Reputation: 113

Fetching array data from jsonb postgresql

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.

Upvotes: 0

Views: 53

Answers (3)

Shivam Arora
Shivam Arora

Reputation: 364

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"'

Upvotes: 1

user330315
user330315

Reputation:

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"]'

Upvotes: 1

Sharif
Sharif

Reputation: 322

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

Upvotes: 1

Related Questions