Postgres 10

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

CREATE TABLE test (id serial, data jsonb);

CREATE TABLE


INSERT INTO test (data) VALUES ('{"subject":[
{
          "test1": "blue",
          "test2": "12",
          "test3": "green"
          },
          {
          "test1": "red",
          "test2": "1234",
          "test3": "green"
          }
]}');

INSERT 0 1


SELECT *
FROM test t, jsonb_array_elements(t.data->'subject') e
WHERE e.value->>'test2' like '%12%';


id
data
value


1
{"subject": [{"test1": "blue", "test2": "12", "test3": "green"}, {"test1": "red", "test2": "1234", "test3": "green"}]}
{"test1": "blue", "test2": "12", "test3": "green"}

1
{"subject": [{"test1": "blue", "test2": "12", "test3": "green"}, {"test1": "red", "test2": "1234", "test3": "green"}]}
{"test1": "red", "test2": "1234", "test3": "green"}



SELECT 2


SELECT * FROM test
WHERE EXISTS (SELECT FROM jsonb_array_elements(test.data->'subject') as s
    WHERE (s->>'test2' = '1234'));


id
data


1
{"subject": [{"test1": "blue", "test2": "12", "test3": "green"}, {"test1": "red", "test2": "1234", "test3": "green"}]}



SELECT 1

id	data	value
1	{"subject": [{"test1": "blue", "test2": "12", "test3": "green"}, {"test1": "red", "test2": "1234", "test3": "green"}]}	{"test1": "blue", "test2": "12", "test3": "green"}
1	{"subject": [{"test1": "blue", "test2": "12", "test3": "green"}, {"test1": "red", "test2": "1234", "test3": "green"}]}	{"test1": "red", "test2": "1234", "test3": "green"}