SQLite 3.39

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table myTable (
  jsonCol json
);

✓
insert into myTable values
('[{"name": "fieldA", "enum": "someValA"},
 {"name": "fieldB", "enum": "someValB"},
 {"name": "fieldC", "enum": "someValC"}]'),
('[{"name": "fieldA", "enum": "someValA"},
 {"name": "fieldC", "enum": "someValC"}]');
✓
select key, value
from myTable, json_each(jsonCol)


key
value


0
{"name":"fieldA","enum":"someValA"}

1
{"name":"fieldB","enum":"someValB"}

2
{"name":"fieldC","enum":"someValC"}

0
{"name":"fieldA","enum":"someValA"}

1
{"name":"fieldC","enum":"someValC"}


with cte as (
  select key, value, json_extract(value,'$.name') as name, json_extract(value,'$.enum') as enum
  from myTable, json_each(jsonCol)
)
select enum
from cte
where name = 'fieldB';


enum


someValB

key	value
0	{"name":"fieldA","enum":"someValA"}
1	{"name":"fieldB","enum":"someValB"}
2	{"name":"fieldC","enum":"someValC"}
0	{"name":"fieldA","enum":"someValA"}
1	{"name":"fieldC","enum":"someValC"}