Postgres 15

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

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE test(id int,en_name varchar, names JSONB);

CREATE TABLE


INSERT INTO test(id,names) VALUES(1,'{
  "short": null,
  "common": [
    {
      "value": "برونشوايغ",
      "language": "ar"
    },
    {
      "value": "برانشویگ",
      "language": "ur"
    },
    {
      "value": "برونشوایگ",
      "language": "fa"
    },
    {
      "value": "Брауншвейг",
      "language": "ru"
    },
    {
      "value": "Breunswyk",
      "language": "fy"
    },
    {
      "value": "Braunšveiga",
      "language": "lv"
    },
    {
      "value": "Brunszwik",
      "language": "pl"
    },
    {
      "value": "Braunschweig",
      "language": "local"
    },
    {
      "value": "Brunswick",
      "language": "fr"
    },
    {
      "value": "Brunswick",
      "language": "es"
    },
    {
      "value": "Brunswick",
      "language": "en"
    }
  
  ]
}');

INSERT 0 1


SELECT * FROM test;


id
en_name
names


1
null
{"short": null, "common": [{"value": "برونشوايغ", "language": "ar"}, {"value": "برانشویگ", "language": "ur"}, {"value": "برونشوایگ", "language": "fa"}, {"value": "Брауншвейг", "language": "ru"}, {"value": "Breunswyk", "language": "fy"}, {"value": "Braunšveiga", "language": "lv"}, {"value": "Brunszwik", "language": "pl"}, {"value": "Braunschweig", "language": "local"}, {"value": "Brunswick", "language": "fr"}, {"value": "Brunswick", "language": "es"}, {"value": "Brunswick", "language": "en"}]}



SELECT 1


SELECT id, name->>'value' AS name_value, name->>'language' AS language
FROM test,
  jsonb_array_elements(names->'common') AS name;


id
name_value
language


1
برونشوايغ
ar

1
برانشویگ
ur

1
برونشوایگ
fa

1
Брауншвейг
ru

1
Breunswyk
fy

1
Braunšveiga
lv

1
Brunszwik
pl

1
Braunschweig
local

1
Brunswick
fr

1
Brunswick
es

1
Brunswick
en



SELECT 11


SELECT id, name->>'value' AS en_name
FROM test,
  jsonb_array_elements(names->'common') AS name
WHERE name->>'language' = 'en';


id
en_name


1
Brunswick



SELECT 1


SELECT id, jsonb_path_query(names, '$.common[*]?(@.language == "en").value') AS en_name
FROM test


id
en_name


1
"Brunswick"



SELECT 1

id	name_value	language
1	برونشوايغ	ar
1	برانشویگ	ur
1	برونشوایگ	fa
1	Брауншвейг	ru
1	Breunswyk	fy
1	Braunšveiga	lv
1	Brunszwik	pl
1	Braunschweig	local
1	Brunswick	fr
1	Brunswick	es
1	Brunswick	en