Better jsonb schema for querying in postgres
There are 2 questions here;



First one is the index structure.


You can use gin(item) here (without jsonb_path_ops). It will be just fine. You are querying  for key (id) only not the full path. 
You can create an expression index (or an gin index on a generated column) for extracting id field values to an integer[] value. Most probably this index will be smaller than jsonb index (~%10 smaller). But I am not sure whether it's faster for your case.


Here is an example for second usage.

Create an immutable function.

CREATE FUNCTION to_my_int_array (jsonb) RETURNS integer[] AS 
$$
  SELECT
      ARRAY(
        SELECT (jsonb_array_elements($1) -&gt;&gt; 'id')::integer
      );

$$
LANGUAGE sql
IMMUTABLE
RETURNS NULL ON NULL INPUT;


Create an expression index with the function above:

CREATE INDEX ON my_table USING gin (to_my_int_array (item));


Filter the query with function;

SELECT
    mt.uid,
    my_item.*
  FROM my_table mt,
  LATERAL jsonb_array_elements(mt.item) ae,
  LATERAL jsonb_to_record(ae) AS my_item (id int, m text, s int)
  WHERE
    to_my_int_array(mt.item) @&gt; '{1}'::integer[];




Second question is the output. The query above also shows output for version 11. After version 12, you can simply use the query below;

SELECT
    uid,
    my_item.*
  FROM my_table mt,
  LATERAL jsonb_to_record(jsonb_path_query_first(mt.item, '$[*] ? (@.id == 1) ')) 
    AS my_item(id int, m text, s int)
  WHERE
    mt.item @&gt; '[{"id": 1}]';

| uid | id  | m   | s   |
| --- | --- | --- | --- |
| 1   | 1   | 123 | 1   |
| 2   | 1   | yes | 2   |
| 3   | 1   | qa  | 1   |