how to fetch the specific array row data using json_array

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

CodePudding user response：

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