Postgres 13

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

WITH mytable AS (
   SELECT '{"a": "value1", "b": [{"b1": "value2", "b3": "value4"}, {"b1": "value5", "b3": "value6"}]}'::json as mydata
)

SELECT
    *
FROM mytable

mydata

{"a": "value1", "b": [{"b1": "value2", "b3": "value4"}, {"b1": "value5", "b3": "value6"}]}

SELECT 1

WITH mytable AS (
   SELECT '{"a": "value1", "b": [{"b1": "value2", "b3": "value4"}, {"b1": "value5", "b3": "value6"}]}'::json as mydata
)

SELECT
    *
FROM mytable,
    json_array_elements(mydata -> 'b') as elements

mydata
value

{"a": "value1", "b": [{"b1": "value2", "b3": "value4"}, {"b1": "value5", "b3": "value6"}]}
{"b1": "value2", "b3": "value4"}

{"a": "value1", "b": [{"b1": "value2", "b3": "value4"}, {"b1": "value5", "b3": "value6"}]}
{"b1": "value5", "b3": "value6"}

SELECT 2

WITH mytable AS (
   SELECT '{"a": "value1", "b": [{"b1": "value2", "b3": "value4"}, {"b1": "value5", "b3": "value6"}]}'::json as mydata
)

SELECT
    elements -> 'b1'
FROM mytable,
    json_array_elements(mydata -> 'b') as elements

?column?

"value2"

"value5"

SELECT 2

WITH mytable AS (
   SELECT '{"a": "value1", "b": [{"b1": "value2", "b3": "value4"}, {"b1": "value5", "b3": "value6"}]}'::json as mydata
)

SELECT
    json_agg(elements -> 'b1')
FROM mytable,
    json_array_elements(mydata -> 'b') as elements

json_agg

["value2", "value5"]

SELECT 1

mydata	value
{"a": "value1", "b": [{"b1": "value2", "b3": "value4"}, {"b1": "value5", "b3": "value6"}]}	{"b1": "value2", "b3": "value4"}
{"a": "value1", "b": [{"b1": "value2", "b3": "value4"}, {"b1": "value5", "b3": "value6"}]}	{"b1": "value5", "b3": "value6"}