how to fetch the specific array row data using json_array_elements

Question

The table I am using has an array of objects, I need to fetch data of supposing 0 row:

create table scientist (id integer, firstname varchar(1000), lastname varchar(100));
insert into scientist (id, firstname, lastname) values (1,'[
   {
      "ch":"1",
      "id":"12",
      "area":"0",
      "level":"Superficial",
      "width":"",
      "length":"",
      "othern":"5",
      "percent":"100",
      "location":" 2nd finger base"
   },
   {
      "ch":"1",
      "id":"13",
      "area":"0",
      "level":"Skin",
      "width":"",
      "length":"",
      "othern":"1",
      "percent":"100",
      "location":" Abdomen "
   }
]', 'einstein');
            
select json_array_elements_text(firstname::json) from scientist

This will return 2 rows of data. How can I get only a specified row of data, suppose I need to get an object where "level":"Superficial"or 0th row data

That does not look like a firstname. And it shouldn't be stored in a varchar(1000) either - use jsonb for JSON data! — Bergi
– Bergi, Commented Oct 10, 2022 at 23:51

Bergi · Accepted Answer · 2022-10-11 00:16:32Z

1

Just use a WHERE clause to get only the row that you want.

You will however need to change your query to a) use json_array_elements instead of json_array_elements_text and b) use a lateral subquery instead of calling the function in the SELECT clause.

SELECT value
FROM scientist, json_array_elements(firstname::json)
WHERE value ->> 'level' = 'Superficial'

^{(online demo)}

answered Oct 11, 2022 at 0:16

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

3 Comments

CoderBeginner Over a year ago

could you please also help me update id =2 where id is 46 select q.a from (select json_array_elements_text('[{"id":46,"value":"a"}]'::json) as a)q. I tried multiple ways but was not able to achieve it.

Bergi Over a year ago

@CoderBeginner You might want to ask a new question about that.

CoderBeginner Over a year ago

stackoverflow.com/questions/74035900/…

Collectives™ on Stack Overflow

how to fetch the specific array row data using json_array_elements

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related