Postgres 16

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

create table tbl_lang (Data) as values 
('{
    "id": 169,
    "status": 1,
    "from_time": "10:30:00",
    "to_time": "19:30:00",
    "created_at": "2023-11-03 18:05:16",
    "updated_at": "2024-03-31 18:11:43",
    "language": ["English", "Hindi", "Punjabi"]
}'::jsonb)
,('{
    "id": 817,
    "status": 1,
    "from_time": "10:00:00",
    "to_time": "17:30:00",
    "created_at": "2024-03-29 17:31:26",
    "updated_at": null,
    "language": ["English", "Hindi"]
}')
,('{
    "id": 999,
    "status": 1,
    "from_time": "11:00:00",
    "to_time": "16:30:00",
    "created_at": "2024-04-02 16:01:26",
    "updated_at": null,
    "language": ["English", "French"]
}')
;
select jsonb_pretty(data) from tbl_lang;

SELECT 3




jsonb_pretty


{
    "id": 169,
    "status": 1,
    "to_time": "19:30:00",
    "language": [
        "English",
        "Hindi",
        "Punjabi"
    ],
    "from_time": "10:30:00",
    "created_at": "2023-11-03 18:05:16",
    "updated_at": "2024-03-31 18:11:43"
}

{
    "id": 817,
    "status": 1,
    "to_time": "17:30:00",
    "language": [
        "English",
        "Hindi"
    ],
    "from_time": "10:00:00",
    "created_at": "2024-03-29 17:31:26",
    "updated_at": null
}

{
    "id": 999,
    "status": 1,
    "to_time": "16:30:00",
    "language": [
        "English",
        "French"
    ],
    "from_time": "11:00:00",
    "created_at": "2024-04-02 16:01:26",
    "updated_at": null
}



SELECT 3


select jsonb_pretty(data) from tbl_lang
where (data->'language') ?& array['English', 'Hindi'];


jsonb_pretty


{
    "id": 169,
    "status": 1,
    "to_time": "19:30:00",
    "language": [
        "English",
        "Hindi",
        "Punjabi"
    ],
    "from_time": "10:30:00",
    "created_at": "2023-11-03 18:05:16",
    "updated_at": "2024-03-31 18:11:43"
}

{
    "id": 817,
    "status": 1,
    "to_time": "17:30:00",
    "language": [
        "English",
        "Hindi"
    ],
    "from_time": "10:00:00",
    "created_at": "2024-03-29 17:31:26",
    "updated_at": null
}



SELECT 2


select setseed(.42);--stabilise random() between re-runs
--generate mock samples
insert into tbl_lang 
select jsonb_build_object(
          'id', (random()*3e3)::int,
          'status',(random()*5)::int,
          'from_time',(now()-random()*'1 day'::interval)::time(0),
          'to_time',(now()-random()*'4 hours'::interval)::time(0),
          'created_at',to_char((now()-random()*'5 years'::interval),
                               'YYYY-MM-DD HH24:MI:SS'),
          'updated_at',case when .2>random() 
                       then to_char((now()-random()*'4 months'::interval),
                                    'YYYY-MM-DD HH24:MI:SS')
                       end,
          'language',array_sample(
                      array['English','Hindi','French',
                            'German','Punjabi','Chinese',
                            'Korean','Japanese','Portuguese',
                            'Spanish','Turkish','Bengali'], 
                      (1+random()*5)::int))
from generate_series(1,8e4);
--inspect samples
select 
  jsonb_pretty(data[1]),
  jsonb_pretty(data[2]),
  jsonb_pretty(data[3]),
  jsonb_pretty(data[4]),
  jsonb_pretty(data[5]),
  jsonb_pretty(data[6]),
  jsonb_pretty(data[7]),
  jsonb_pretty(data[8])
from (
  select (array_agg(data))[:8] as data
  from tbl_lang tablesample system(.42)repeatable(.42)
  group by split_part(ctid::text,',',1)
  limit 3) as a;


setseed






SELECT 1



INSERT 0 80000




jsonb_pretty
jsonb_pretty
jsonb_pretty
jsonb_pretty
jsonb_pretty
jsonb_pretty
jsonb_pretty
jsonb_pretty


{
    "id": 2746,
    "status": 1,
    "to_time": "06:17:53",
    "language": [
        "English",
        "Portuguese",
        "Chinese",
        "French"
    ],
    "from_time": "03:20:22",
    "created_at": "2023-04-21 16:44:16",
    "updated_at": null
}
{
    "id": 1292,
    "status": 2,
    "to_time": "06:31:01",
    "language": [
        "English",
        "Korean",
        "Spanish"
    ],
    "from_time": "01:31:34",
    "created_at": "2022-06-29 14:34:22",
    "updated_at": null
}
{
    "id": 368,
    "status": 1,
    "to_time": "06:27:46",
    "language": [
        "Punjabi",
        "Japanese",
        "French"
    ],
    "from_time": "00:46:13",
    "created_at": "2020-11-22 16:21:02",
    "updated_at": "2024-02-06 05:40:00"
}
{
    "id": 2872,
    "status": 0,
    "to_time": "08:02:30",
    "language": [
        "Japanese",
        "German",
        "Portuguese",
        "Chinese",
        "Turkish"
    ],
    "from_time": "08:58:16",
    "created_at": "2021-10-27 04:45:23",
    "updated_at": null
}
{
    "id": 733,
    "status": 5,
    "to_time": "08:54:14",
    "language": [
        "Spanish",
        "Japanese",
        "Portuguese"
    ],
    "from_time": "07:21:41",
    "created_at": "2019-09-08 11:34:23",
    "updated_at": null
}
{
    "id": 1492,
    "status": 2,
    "to_time": "09:08:49",
    "language": [
        "Punjabi",
        "French",
        "Turkish"
    ],
    "from_time": "11:46:04",
    "created_at": "2023-01-18 17:00:46",
    "updated_at": null
}
{
    "id": 2700,
    "status": 0,
    "to_time": "05:25:48",
    "language": [
        "Korean",
        "English",
        "Spanish",
        "Portuguese"
    ],
    "from_time": "02:49:47",
    "created_at": "2023-02-02 16:23:13",
    "updated_at": null
}
{
    "id": 1404,
    "status": 3,
    "to_time": "08:02:29",
    "language": [
        "Spanish",
        "Turkish",
        "French",
        "Portuguese"
    ],
    "from_time": "00:33:55",
    "created_at": "2023-11-21 21:20:26",
    "updated_at": null
}

{
    "id": 1132,
    "status": 2,
    "to_time": "08:34:19",
    "language": [
        "Chinese",
        "English",
        "Korean",
        "French",
        "Bengali",
        "Japanese"
    ],
    "from_time": "05:24:01",
    "created_at": "2024-01-24 06:39:59",
    "updated_at": null
}
{
    "id": 2542,
    "status": 3,
    "to_time": "08:20:39",
    "language": [
        "Turkish",
        "Bengali",
        "Japanese"
    ],
    "from_time": "20:53:47",
    "created_at": "2022-06-22 03:45:34",
    "updated_at": null
}
{
    "id": 1612,
    "status": 1,
    "to_time": "09:23:18",
    "language": [
        "Hindi"
    ],
    "from_time": "23:22:33",
    "created_at": "2023-02-23 22:48:21",
    "updated_at": null
}
{
    "id": 1051,
    "status": 2,
    "to_time": "07:47:25",
    "language": [
        "Korean",
        "Hindi",
        "Turkish"
    ],
    "from_time": "23:21:21",
    "created_at": "2022-05-22 17:46:05",
    "updated_at": null
}
{
    "id": 2506,
    "status": 1,
    "to_time": "06:17:51",
    "language": [
        "Japanese",
        "German",
        "Portuguese"
    ],
    "from_time": "18:44:59",
    "created_at": "2022-03-05 09:47:54",
    "updated_at": null
}
{
    "id": 2775,
    "status": 2,
    "to_time": "08:49:51",
    "language": [
        "Bengali",
        "Punjabi",
        "Portuguese",
        "Korean"
    ],
    "from_time": "17:39:21",
    "created_at": "2019-09-15 00:51:08",
    "updated_at": "2024-03-03 06:44:14"
}
{
    "id": 1201,
    "status": 2,
    "to_time": "08:11:54",
    "language": [
        "English",
        "Punjabi",
        "Chinese"
    ],
    "from_time": "00:41:43",
    "created_at": "2022-06-20 23:00:12",
    "updated_at": null
}
{
    "id": 3000,
    "status": 2,
    "to_time": "07:54:06",
    "language": [
        "Chinese"
    ],
    "from_time": "13:24:14",
    "created_at": "2023-12-08 07:35:32",
    "updated_at": null
}

{
    "id": 2897,
    "status": 1,
    "to_time": "08:22:49",
    "language": [
        "Japanese",
        "German"
    ],
    "from_time": "06:05:50",
    "created_at": "2024-01-04 11:14:38",
    "updated_at": null
}
{
    "id": 705,
    "status": 4,
    "to_time": "09:20:30",
    "language": [
        "Bengali",
        "German",
        "English",
        "Japanese",
        "Portuguese",
        "Spanish"
    ],
    "from_time": "22:51:41",
    "created_at": "2022-08-04 13:01:21",
    "updated_at": null
}
{
    "id": 2460,
    "status": 2,
    "to_time": "06:29:28",
    "language": [
        "Punjabi",
        "Turkish",
        "Korean",
        "Spanish"
    ],
    "from_time": "17:38:27",
    "created_at": "2021-07-11 21:14:00",
    "updated_at": null
}
{
    "id": 2251,
    "status": 1,
    "to_time": "05:30:33",
    "language": [
        "Spanish",
        "Hindi",
        "Portuguese"
    ],
    "from_time": "11:50:19",
    "created_at": "2020-08-02 19:39:28",
    "updated_at": null
}
{
    "id": 1367,
    "status": 0,
    "to_time": "06:40:38",
    "language": [
        "Hindi",
        "French",
        "Chinese"
    ],
    "from_time": "01:10:09",
    "created_at": "2024-03-02 05:09:06",
    "updated_at": null
}
{
    "id": 366,
    "status": 0,
    "to_time": "08:53:24",
    "language": [
        "English",
        "German",
        "Bengali",
        "Hindi",
        "Japanese",
        "Turkish"
    ],
    "from_time": "01:26:08",
    "created_at": "2020-08-11 20:56:15",
    "updated_at": null
}
{
    "id": 424,
    "status": 2,
    "to_time": "08:02:28",
    "language": [
        "Bengali",
        "Korean",
        "Turkish",
        "Portuguese"
    ],
    "from_time": "07:53:45",
    "created_at": "2023-07-07 00:48:36",
    "updated_at": null
}
{
    "id": 1148,
    "status": 2,
    "to_time": "05:29:27",
    "language": [
        "English",
        "Spanish",
        "German",
        "Korean"
    ],
    "from_time": "17:09:54",
    "created_at": "2019-04-23 08:43:55",
    "updated_at": "2024-01-03 09:51:52"
}



SELECT 3


alter table tbl_lang alter column "data" type json using "data"::json;

ALTER TABLE


vacuum analyze tbl_lang;

VACUUM


--sequential scan on `json` is slower than on `jsonb`
explain analyze verbose 
select * from tbl_lang
where (data->'language')::jsonb ?& array['English', 'Hindi'];


QUERY PLAN


Seq Scan on public.tbl_lang  (cost=0.00..3840.06 rows=800 width=189) (actual time=0.051..436.639 rows=6617 loops=1)

  Output: data

  Filter: (((tbl_lang.data -> 'language'::text))::jsonb ?& '{English,Hindi}'::text[])

  Rows Removed by Filter: 73386

Planning Time: 0.385 ms

Execution Time: 437.041 ms



EXPLAIN


alter table tbl_lang alter column "data" type jsonb using "data"::jsonb;

ALTER TABLE


vacuum analyze tbl_lang;

VACUUM


--back on `jsonb`, even sequential scans are faster than on `json`
explain analyze verbose 
select jsonb_pretty(data) from tbl_lang
where (data->'language') ?& array['English', 'Hindi'];


QUERY PLAN


Seq Scan on public.tbl_lang  (cost=0.00..3634.05 rows=800 width=32) (actual time=0.029..42.131 rows=6617 loops=1)

  Output: jsonb_pretty(data)

  Filter: ((tbl_lang.data -> 'language'::text) ?& '{English,Hindi}'::text[])

  Rows Removed by Filter: 73386

Planning Time: 0.131 ms

Execution Time: 42.422 ms



EXPLAIN


create index on tbl_lang using gin((data->'language') jsonb_ops);

CREATE INDEX


vacuum analyze tbl_lang;

VACUUM


--with a GIN expression index on a `jsonb` column, index scans are even faster
explain analyze verbose 
select jsonb_pretty(data) from tbl_lang
where (data->'language') ?& array['English', 'Hindi'];


QUERY PLAN


Bitmap Heap Scan on public.tbl_lang  (cost=92.02..2659.95 rows=7996 width=32) (actual time=3.079..22.431 rows=6617 loops=1)

  Output: jsonb_pretty(data)

  Recheck Cond: ((tbl_lang.data -> 'language'::text) ?& '{English,Hindi}'::text[])

  Heap Blocks: exact=2284

  ->  Bitmap Index Scan on tbl_lang_expr_idx  (cost=0.00..90.03 rows=7996 width=0) (actual time=2.735..2.735 rows=6617 loops=1)

        Index Cond: ((tbl_lang.data -> 'language'::text) ?& '{English,Hindi}'::text[])

Planning Time: 0.306 ms

Execution Time: 22.810 ms



EXPLAIN

jsonb_pretty	jsonb_pretty	jsonb_pretty	jsonb_pretty	jsonb_pretty	jsonb_pretty	jsonb_pretty	jsonb_pretty
{ "id": 2746, "status": 1, "to_time": "06:17:53", "language": [ "English", "Portuguese", "Chinese", "French" ], "from_time": "03:20:22", "created_at": "2023-04-21 16:44:16", "updated_at": null }	{ "id": 1292, "status": 2, "to_time": "06:31:01", "language": [ "English", "Korean", "Spanish" ], "from_time": "01:31:34", "created_at": "2022-06-29 14:34:22", "updated_at": null }	{ "id": 368, "status": 1, "to_time": "06:27:46", "language": [ "Punjabi", "Japanese", "French" ], "from_time": "00:46:13", "created_at": "2020-11-22 16:21:02", "updated_at": "2024-02-06 05:40:00" }	{ "id": 2872, "status": 0, "to_time": "08:02:30", "language": [ "Japanese", "German", "Portuguese", "Chinese", "Turkish" ], "from_time": "08:58:16", "created_at": "2021-10-27 04:45:23", "updated_at": null }	{ "id": 733, "status": 5, "to_time": "08:54:14", "language": [ "Spanish", "Japanese", "Portuguese" ], "from_time": "07:21:41", "created_at": "2019-09-08 11:34:23", "updated_at": null }	{ "id": 1492, "status": 2, "to_time": "09:08:49", "language": [ "Punjabi", "French", "Turkish" ], "from_time": "11:46:04", "created_at": "2023-01-18 17:00:46", "updated_at": null }	{ "id": 2700, "status": 0, "to_time": "05:25:48", "language": [ "Korean", "English", "Spanish", "Portuguese" ], "from_time": "02:49:47", "created_at": "2023-02-02 16:23:13", "updated_at": null }	{ "id": 1404, "status": 3, "to_time": "08:02:29", "language": [ "Spanish", "Turkish", "French", "Portuguese" ], "from_time": "00:33:55", "created_at": "2023-11-21 21:20:26", "updated_at": null }
{ "id": 1132, "status": 2, "to_time": "08:34:19", "language": [ "Chinese", "English", "Korean", "French", "Bengali", "Japanese" ], "from_time": "05:24:01", "created_at": "2024-01-24 06:39:59", "updated_at": null }	{ "id": 2542, "status": 3, "to_time": "08:20:39", "language": [ "Turkish", "Bengali", "Japanese" ], "from_time": "20:53:47", "created_at": "2022-06-22 03:45:34", "updated_at": null }	{ "id": 1612, "status": 1, "to_time": "09:23:18", "language": [ "Hindi" ], "from_time": "23:22:33", "created_at": "2023-02-23 22:48:21", "updated_at": null }	{ "id": 1051, "status": 2, "to_time": "07:47:25", "language": [ "Korean", "Hindi", "Turkish" ], "from_time": "23:21:21", "created_at": "2022-05-22 17:46:05", "updated_at": null }	{ "id": 2506, "status": 1, "to_time": "06:17:51", "language": [ "Japanese", "German", "Portuguese" ], "from_time": "18:44:59", "created_at": "2022-03-05 09:47:54", "updated_at": null }	{ "id": 2775, "status": 2, "to_time": "08:49:51", "language": [ "Bengali", "Punjabi", "Portuguese", "Korean" ], "from_time": "17:39:21", "created_at": "2019-09-15 00:51:08", "updated_at": "2024-03-03 06:44:14" }	{ "id": 1201, "status": 2, "to_time": "08:11:54", "language": [ "English", "Punjabi", "Chinese" ], "from_time": "00:41:43", "created_at": "2022-06-20 23:00:12", "updated_at": null }	{ "id": 3000, "status": 2, "to_time": "07:54:06", "language": [ "Chinese" ], "from_time": "13:24:14", "created_at": "2023-12-08 07:35:32", "updated_at": null }
{ "id": 2897, "status": 1, "to_time": "08:22:49", "language": [ "Japanese", "German" ], "from_time": "06:05:50", "created_at": "2024-01-04 11:14:38", "updated_at": null }	{ "id": 705, "status": 4, "to_time": "09:20:30", "language": [ "Bengali", "German", "English", "Japanese", "Portuguese", "Spanish" ], "from_time": "22:51:41", "created_at": "2022-08-04 13:01:21", "updated_at": null }	{ "id": 2460, "status": 2, "to_time": "06:29:28", "language": [ "Punjabi", "Turkish", "Korean", "Spanish" ], "from_time": "17:38:27", "created_at": "2021-07-11 21:14:00", "updated_at": null }	{ "id": 2251, "status": 1, "to_time": "05:30:33", "language": [ "Spanish", "Hindi", "Portuguese" ], "from_time": "11:50:19", "created_at": "2020-08-02 19:39:28", "updated_at": null }	{ "id": 1367, "status": 0, "to_time": "06:40:38", "language": [ "Hindi", "French", "Chinese" ], "from_time": "01:10:09", "created_at": "2024-03-02 05:09:06", "updated_at": null }	{ "id": 366, "status": 0, "to_time": "08:53:24", "language": [ "English", "German", "Bengali", "Hindi", "Japanese", "Turkish" ], "from_time": "01:26:08", "created_at": "2020-08-11 20:56:15", "updated_at": null }	{ "id": 424, "status": 2, "to_time": "08:02:28", "language": [ "Bengali", "Korean", "Turkish", "Portuguese" ], "from_time": "07:53:45", "created_at": "2023-07-07 00:48:36", "updated_at": null }	{ "id": 1148, "status": 2, "to_time": "05:29:27", "language": [ "English", "Spanish", "German", "Korean" ], "from_time": "17:09:54", "created_at": "2019-04-23 08:43:55", "updated_at": "2024-01-03 09:51:52" }