Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE end_user (
  id text NOT NULL
, environment_id text NOT NULL
, profile jsonb NOT NULL DEFAULT '{}'::jsonb
, CONSTRAINT end_user_pkey PRIMARY KEY (environment_id, id)
);
INSERT INTO end_user VALUES
  ('1', 'test', '{"timezone": "NZ/Auckland", "fruit": "cherry"}')
, ('2', 'foo', '{"name": "Somedata", "timezone": "Vienna"}')
;
CREATE INDEX end_user_path_ops_idx ON end_user USING GIN (profile jsonb_path_ops);

CREATE TABLE

INSERT 0 2

CREATE INDEX

SELECT *
FROM   end_user u
WHERE  environment_id = 'test'
AND    profile @? '$.timezone ? (@ like_regex "auckland" flag "i")';

id
environment_id
profile

1
test
{"fruit": "cherry", "timezone": "NZ/Auckland"}

SELECT 1

-- to show that index can be used
set enable_seqscan = off;

EXPLAIN
SELECT *
FROM   end_user u
WHERE  profile @? '$.timezone ? (@ like_regex "auckland" flag "i")';

SET

QUERY PLAN

Bitmap Heap Scan on end_user u  (cost=20.00..24.01 rows=1 width=96)

  Recheck Cond: (profile @? '$."timezone"?(@ like_regex "auckland" flag "i")'::jsonpath)

  ->  Bitmap Index Scan on end_user_path_ops_idx  (cost=0.00..20.00 rows=1 width=0)

        Index Cond: (profile @? '$."timezone"?(@ like_regex "auckland" flag "i")'::jsonpath)

EXPLAIN