Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table mytable (
  id int,
  result jsonb
);

insert into mytable values
(1 , '{"condition": "OR", "duplicate_ids": {"book_name": {"value": "-", "duplicates": ["afec4e99", "4c86040b"]}, "author": {"value": "-", "duplicates": ["4c86040b", "362a883d", "7856b483", "7b091646", "5c99eea3", "0fa4f47f"]}}}'),
(1 , '{"condition": "OR", "duplicate_ids": {"author": {"value": "some_name", "duplicates": ["69e139df"]}}}'),
(2 , '{"condition": "OR", "duplicate_ids": {"author": {"value": "other_name", "duplicates": ["7fa13aa8"]}}}'),                                                    
(3 , '{"condition": "OR", "duplicate_ids": {"publisher": {"value": "publisher_name", "duplicates": ["2b69af3d"]}}}');                                                  

CREATE TABLE

INSERT 0 4

select result->'duplicate_ids'
from mytable
where id = 1

?column?

{"author": {"value": "-", "duplicates": ["4c86040b", "362a883d", "7856b483", "7b091646", "5c99eea3", "0fa4f47f"]}, "book_name": {"value": "-", "duplicates": ["afec4e99", "4c86040b"]}}

{"author": {"value": "some_name", "duplicates": ["69e139df"]}}

SELECT 2

SELECT
    e.value -> 'duplicates'
FROM mytable t
CROSS JOIN jsonb_each(result->'duplicate_ids') e
WHERE t.id = 1

?column?

["4c86040b", "362a883d", "7856b483", "7b091646", "5c99eea3", "0fa4f47f"]

["afec4e99", "4c86040b"]

["69e139df"]

SELECT 3

SELECT jsonb_array_elements_text(e.value -> 'duplicates') AS duplicates 
FROM mytable t
CROSS JOIN jsonb_each(result->'duplicate_ids') e 
WHERE t.id = 1;

duplicates

4c86040b

362a883d

7856b483

7b091646

5c99eea3

0fa4f47f

afec4e99

4c86040b

69e139df

SELECT 9