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"
    },
    {

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, names->'common'->0->>'value' AS name FROM test;


id
name


1
برونشوايغ



SELECT 1


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


id
en_name


1
null



SELECT 1


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

ERROR:  syntax error at or near "JSONB_ARRAY_ELEMENTS"
LINE 4:   JSONB_ARRAY_ELEMENTS(common->'value') as name_value,
          ^


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

ERROR:  operator is not unique: unknown ->> unknown
LINE 1: SELECT id,'common'->>'value' AS en_name
                          ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.