Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl1 (material_id int, codes jsonb);
INSERT INTO tbl1 VALUES
  (1, '["A-12","B-19","A-14","X-22"]')
, (2, '["X-106","A-12","X-22","B-19"]')
, (3, '["X-106","A-66","X-22","B-77"]');  -- 3rd row to make demo more useful
;
CREATE TABLE tbl2 (user_id int, material_list jsonb);
INSERT INTO tbl2 VALUES
  (1, '[2,3]')
, (2, '[1,2]')
, (3, '[7,8]');  -- 3rd row makes demo more useful

CREATE TABLE

INSERT 0 3

CREATE TABLE

INSERT 0 3

SELECT ARRAY(SELECT material_id FROM tbl1 where codes ?| array['A-12','B-19']);

array

{1,2}

SELECT 1

SELECT ARRAY(SELECT material_id FROM tbl1 where codes ?| '{A-12,B-19}');

array

{1,2}

SELECT 1

SELECT user_id
FROM   tbl2
WHERE  ARRAY(SELECT jsonb_array_elements_text(material_list)::int)
    && ARRAY(SELECT material_id FROM tbl1 where codes ?| array['A-12','B-19']);

user_id

1

2

SELECT 2

-- helper to convert to int[]
CREATE OR REPLACE FUNCTION jsonb_arr2int_arr(_js jsonb)
  RETURNS int[] LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT ARRAY(SELECT jsonb_array_elements_text(_js)::int)';

CREATE FUNCTION

-- convert to int[]
ALTER TABLE tbl2
  ALTER COLUMN material_list TYPE int[] USING jsonb_arr2int_arr(material_list);

ALTER TABLE

-- simpler now:
SELECT user_id
FROM   tbl2
WHERE  material_list && ARRAY(SELECT material_id FROM tbl1 where codes ?| '{A-12,B-19}');

user_id

1

2

SELECT 2