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, 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