Postgres 14

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 foo_json (
  id uuid PRIMARY KEY,
  work_experience jsonb NOT NULL
);
INSERT INTO foo_json (id, work_experience)
VALUES (
  'b4e942a0-49b4-4fa7-8f7a-5fbf0541d1c9',
  '[ {"id": "7cd74bae-ff5b-4f58-ab20-0218f820ffff", "skills":
  [{"id": "c8a5328d-87ba-419d-802f-80b1d940bb68|313384d2-486d-4b7f-ba33-76b1cd696f0a|fd3c41b8-8c15-47e2-a80d-cf3683b2d0da", "level1": "Programming languages", "level2": "Scripting languages", "level3": "TypeScript", "level1_id": "c8a5328d-87ba-419d-802f-80b1d940bb68", "level2_id": "313384d2-486d-4b7f-ba33-76b1cd696f0a", "level3_id": "fd3c41b8-8c15-47e2-a80d-cf3683b2d0da"}, {"id": "84dff39f-2ed6-408c-a188-14cf55a09c5b|e13b48c1-fd0f-4ecc-89de-58e9312b9435|686af7e4-6d58-4148-b227-3bf65ff10273", "level1": "Software frameworks & libraries", "level2": "Frontend frameworks & libraries", "level3": "React", "level1_id": "84dff39f-2ed6-408c-a188-14cf55a09c5b", "level2_id": "e13b48c1-fd0f-4ecc-89de-58e9312b9435", "level3_id": "686af7e4-6d58-4148-b227-3bf65ff10273"}, {"id": "84dff39f-2ed6-408c-a188-14cf55a09c5b|c4e54726-7bd5-44bb-8597-a05eb2272e2b|cda4441f-dba6-495c-9e2e-7429bd5e0465", "level1": "Software frameworks & libraries", "level2": "Backend frameworks & libraries", "level3": "Node.js", "level1_id": "84dff39f-2ed6-408c-a188-14cf55a09c5b", "level2_id": "c4e54726-7bd5-44bb-8597-a05eb2272e2b", "level3_id": "cda4441f-dba6-495c-9e2e-7429bd5e0465"}, {"id": "84dff39f-2ed6-408c-a188-14cf55a09c5b|e13b48c1-fd0f-4ecc-89de-58e9312b9435|fd3c41b8-8c15-47e2-a80d-cf3683b2d0da", "level1": "Software frameworks & libraries", "level2": "Frontend frameworks & libraries", "level3": "TypeScript", "level1_id": "84dff39f-2ed6-408c-a188-14cf55a09c5b", "level2_id": "e13b48c1-fd0f-4ecc-89de-58e9312b9435", "level3_id": "fd3c41b8-8c15-47e2-a80d-cf3683b2d0da"}], "end_date": null, "position": "Senior Software Engineer + Team Lead", "start_date": "2019-10-01T00:00:00", "description": "Draper, Utah, United Statesn• Architected Expert Portal* from the ground up using a Node/Typescriptnbackend, anPostgreSQL database, a GraphQL API layer, a Webpack build process, with anTypescript/React front-end and XState for state managementn• Enforced coding best practices with linting rules and code formatters bynautomating it inngit workflown• Automated deployment Expert Portal* to EC2 instances and the @pluralsightnNPMnartifactory using Github, TeamCity, and Octopusn• Improved product team workflow by building a browser extention to addnLeanKit cardntemplate functionalityn• Consumed and published data through Kafka streams and RabbitMQnmessagesn• Interviewed, onboarded, and trained junior to mid-level engineers", "company_name": "Pluralsight"},
{"id": "9e2c2b44-39a4-4369-b237-c51fd938e61d", "skills": [{"id": "c8a5328d-87ba-419d-802f-80b1d940bb68|313384d2-486d-4b7f-ba33-76b1cd696f0a|012abcd1-3a6a-4803-a47e-42f46b402024", "level1": "Programming languages", "level2": "Scripting languages", "level3": "JavaScript", "level1_id": "c8a5328d-87ba-419d-802f-80b1d940bb68", "level2_id": "313384d2-486d-4b7f-ba33-76b1cd696f0a", "level3_id": "012abcd1-3a6a-4803-a47e-42f46b402024"}], "end_date": "2019-10-01T00:00:00", "position": "Software Engineer II", "start_date": "2017-11-01T00:00:00", "description": "Greater Salt Lake City AreanWorked on the KSL Jobs Classifieds team as a full-stack developer. Followingnthe scrum methodology, I added new features and maintained all thingsnpowered by KSL Jobs.n• Built and deployed a \"white label\" version of KSL Jobs for the Silicon Slopesnbrand. (https://siliconslopes.ksl.com)n• Rewrote major sections of the current KSL Jobs site in React.jsn• Automated querying data for reports and analytic purposes through Node andnPHP scriptsn• Provided rich data tracking through Google Tag Manager, Google Analytics,nand BigQueryn• Migrated Solr search engine to ElasticSearch with a GraphQL API", "company_name": "Deseret Digital Media"},
{"id": "efbf68f4-7bdc-4ab6-bba9-fbf7ec38aeef",
      "skills": [{"id": "c8a5328d-87ba-419d-802f-80b1d940bb68|f45abc59-1e7e-4853-a498-999fcf12d498|4b266297-6e25-4443-90ec-248bded4225a",
      "level1": "Programming languages",
      "level2": "High-level languages",
      "level3": "PHP", "level1_id": "c8a5328d-87ba-419d-802f-80b1d940bb68",
      "level2_id": "f45abc59-1e7e-4853-a498-999fcf12d498", "level3_id": "4b266297-6e25-4443-90ec-248bded4225a"}],
      "end_date": "2019-08-01T00:00:00",
      "position": "Full Stack Developer",
    "start_date": "2017-01-01T00:00:00",
      "description": "Provo, Utah AreanWorked with Appritech LLC to modernize their legacy software and add newnfeatures to automate their business processes.n•  Building new call handler from the ground up using JavaScript ES6,nBootstrap, SASS fornfront-end, and PHP7/Laravel for back-endn•  Implemented real-time call management system and built reporting API.nImproved call agentnproductivity by 70%n•  Upgraded deprecated PHP legacy code to PHP 7n•  Synchronized follow up calls with Twilio API functionality for SMS messagenforwardingn•  Installed and setup Apache server for an after hours call center",
    "company_name": "Appritech Software"},
    {"id": "2db60c6c-c214-4d9b-9034-baba676203a8", "skills": [{"id": "c8a5328d-87ba-419d-802f-80b1d940bb68|f45abc59-1e7e-4853-a498-999fcf12d498|4b266297-6e25-4443-90ec-248bded4225a",
    "level1": "Programming languages",
    "level2": "High-level languages", "level3": "PHP",
    "level1_id": "c8a5328d-87ba-419d-802f-80b1d940bb68",
    "level2_id": "f45abc59-1e7e-4853-a498-999fcf12d498", "level3_id": "4b266297-6e25-4443-90ec-248bded4225a"}],
    "end_date": "2017-09-01T00:00:00", "position": "Web Developer", "start_date": "2017-02-01T00:00:00",
    "description": "Provo, Utah AreanWorked with Redcore LLC to build entrepreneurial tools, marketing tools, andnadding new functionality to the current Wordpress-integrated website andnCMS.n• Built Brand Management website from the ground up using Bootstrap andnJavaScript on thenfront end, with PHP and MySQL on the back-endn• Automated managerial and accounting tasks, such as invoice generation,nand transactions using Stripe APIn• Debugged and perfected current web applications to enrich UXn• Doubled clientele by expanding Redcore services offered to include websitencreation andnmanagement",
    "company_name": "Redcore LLC"},

{"id": "c3f1d5b2-5586-477d-ae4c-e2927463244e",
    "skills": [{"id": "c8a5328d-87ba-419d-802f-80b1d940bb68|313384d2-486d-4b7f-ba33-76b1cd696f0a|012abcd1-3a6a-4803-a47e-42f46b402024",
    "level1": "Programming languages",
    "level2": "Scripting languages",
    "level3": "JavaScript",
    "level1_id": "c8a5328d-87ba-419d-802f-80b1d940bb68",
    "level2_id": "313384d2-486d-4b7f-ba33-76b1cd696f0a",
    "level3_id": "012abcd1-3a6a-4803-a47e-42f46b402024"}],
     "end_date": "2017-03-01T00:00:00", "position": "Software and Web Developer",
    "start_date": "2016-12-01T00:00:00",
    "description": "Provo, Utah AreanThis internship was focused on building core website and software fornCentaurific using the LAMP stack.n• Created dynamic landing pages for products to generate leadsn• Designed a MySQL database to organize data and generate excel reportsn• Revitalized marketing efforts through analytics and a social media sharingnpage",
    "company_name": "Centaurific"} ]'
);

CREATE TABLE



INSERT 0 1


SELECT workexp['skills']
FROM foo_json,
jsonb_array_elements(work_experience) workexp;


workexp


[{"id": "c8a5328d-87ba-419d-802f-80b1d940bb68|313384d2-486d-4b7f-ba33-76b1cd696f0a|fd3c41b8-8c15-47e2-a80d-cf3683b2d0da", "level1": "Programming languages", "level2": "Scripting languages", "level3": "TypeScript", "level1_id": "c8a5328d-87ba-419d-802f-80b1d940bb68", "level2_id": "313384d2-486d-4b7f-ba33-76b1cd696f0a", "level3_id": "fd3c41b8-8c15-47e2-a80d-cf3683b2d0da"}, {"id": "84dff39f-2ed6-408c-a188-14cf55a09c5b|e13b48c1-fd0f-4ecc-89de-58e9312b9435|686af7e4-6d58-4148-b227-3bf65ff10273", "level1": "Software frameworks & libraries", "level2": "Frontend frameworks & libraries", "level3": "React", "level1_id": "84dff39f-2ed6-408c-a188-14cf55a09c5b", "level2_id": "e13b48c1-fd0f-4ecc-89de-58e9312b9435", "level3_id": "686af7e4-6d58-4148-b227-3bf65ff10273"}, {"id": "84dff39f-2ed6-408c-a188-14cf55a09c5b|c4e54726-7bd5-44bb-8597-a05eb2272e2b|cda4441f-dba6-495c-9e2e-7429bd5e0465", "level1": "Software frameworks & libraries", "level2": "Backend frameworks & libraries", "level3": "Node.js", "level1_id": "84dff39f-2ed6-408c-a188-14cf55a09c5b", "level2_id": "c4e54726-7bd5-44bb-8597-a05eb2272e2b", "level3_id": "cda4441f-dba6-495c-9e2e-7429bd5e0465"}, {"id": "84dff39f-2ed6-408c-a188-14cf55a09c5b|e13b48c1-fd0f-4ecc-89de-58e9312b9435|fd3c41b8-8c15-47e2-a80d-cf3683b2d0da", "level1": "Software frameworks & libraries", "level2": "Frontend frameworks & libraries", "level3": "TypeScript", "level1_id": "84dff39f-2ed6-408c-a188-14cf55a09c5b", "level2_id": "e13b48c1-fd0f-4ecc-89de-58e9312b9435", "level3_id": "fd3c41b8-8c15-47e2-a80d-cf3683b2d0da"}]

[{"id": "c8a5328d-87ba-419d-802f-80b1d940bb68|313384d2-486d-4b7f-ba33-76b1cd696f0a|012abcd1-3a6a-4803-a47e-42f46b402024", "level1": "Programming languages", "level2": "Scripting languages", "level3": "JavaScript", "level1_id": "c8a5328d-87ba-419d-802f-80b1d940bb68", "level2_id": "313384d2-486d-4b7f-ba33-76b1cd696f0a", "level3_id": "012abcd1-3a6a-4803-a47e-42f46b402024"}]

[{"id": "c8a5328d-87ba-419d-802f-80b1d940bb68|f45abc59-1e7e-4853-a498-999fcf12d498|4b266297-6e25-4443-90ec-248bded4225a", "level1": "Programming languages", "level2": "High-level languages", "level3": "PHP", "level1_id": "c8a5328d-87ba-419d-802f-80b1d940bb68", "level2_id": "f45abc59-1e7e-4853-a498-999fcf12d498", "level3_id": "4b266297-6e25-4443-90ec-248bded4225a"}]

[{"id": "c8a5328d-87ba-419d-802f-80b1d940bb68|f45abc59-1e7e-4853-a498-999fcf12d498|4b266297-6e25-4443-90ec-248bded4225a", "level1": "Programming languages", "level2": "High-level languages", "level3": "PHP", "level1_id": "c8a5328d-87ba-419d-802f-80b1d940bb68", "level2_id": "f45abc59-1e7e-4853-a498-999fcf12d498", "level3_id": "4b266297-6e25-4443-90ec-248bded4225a"}]

[{"id": "c8a5328d-87ba-419d-802f-80b1d940bb68|313384d2-486d-4b7f-ba33-76b1cd696f0a|012abcd1-3a6a-4803-a47e-42f46b402024", "level1": "Programming languages", "level2": "Scripting languages", "level3": "JavaScript", "level1_id": "c8a5328d-87ba-419d-802f-80b1d940bb68", "level2_id": "313384d2-486d-4b7f-ba33-76b1cd696f0a", "level3_id": "012abcd1-3a6a-4803-a47e-42f46b402024"}]



SELECT 5


--query skills->id,  skills->level1, skills->level2
with a as (
SELECT workexp
FROM foo_json,
jsonb_array_elements(work_experience) workexp),
b as(select jsonb_array_elements(workexp['skills']) last_level, count(*) over() from a)
select last_level['id'],last_level['level1'] , last_level['level2'] from b;


last_level
last_level
last_level


"c8a5328d-87ba-419d-802f-80b1d940bb68|313384d2-486d-4b7f-ba33-76b1cd696f0a|fd3c41b8-8c15-47e2-a80d-cf3683b2d0da"
"Programming languages"
"Scripting languages"

"84dff39f-2ed6-408c-a188-14cf55a09c5b|e13b48c1-fd0f-4ecc-89de-58e9312b9435|686af7e4-6d58-4148-b227-3bf65ff10273"
"Software frameworks & libraries"
"Frontend frameworks & libraries"

"84dff39f-2ed6-408c-a188-14cf55a09c5b|c4e54726-7bd5-44bb-8597-a05eb2272e2b|cda4441f-dba6-495c-9e2e-7429bd5e0465"
"Software frameworks & libraries"
"Backend frameworks & libraries"

"84dff39f-2ed6-408c-a188-14cf55a09c5b|e13b48c1-fd0f-4ecc-89de-58e9312b9435|fd3c41b8-8c15-47e2-a80d-cf3683b2d0da"
"Software frameworks & libraries"
"Frontend frameworks & libraries"

"c8a5328d-87ba-419d-802f-80b1d940bb68|313384d2-486d-4b7f-ba33-76b1cd696f0a|012abcd1-3a6a-4803-a47e-42f46b402024"
"Programming languages"
"Scripting languages"

"c8a5328d-87ba-419d-802f-80b1d940bb68|f45abc59-1e7e-4853-a498-999fcf12d498|4b266297-6e25-4443-90ec-248bded4225a"
"Programming languages"
"High-level languages"

"c8a5328d-87ba-419d-802f-80b1d940bb68|f45abc59-1e7e-4853-a498-999fcf12d498|4b266297-6e25-4443-90ec-248bded4225a"
"Programming languages"
"High-level languages"

"c8a5328d-87ba-419d-802f-80b1d940bb68|313384d2-486d-4b7f-ba33-76b1cd696f0a|012abcd1-3a6a-4803-a47e-42f46b402024"
"Programming languages"
"Scripting languages"



SELECT 8


--query filter based on mainid (not the inner nested)
with a as (
SELECT workexp
FROM foo_json,
jsonb_array_elements(work_experience) workexp),
b as(select workexp['id'] mainid, workexp['skills'], count(*) over() from  a)
select *, pg_typeof(mainid) from b 
where mainid::text = '"7cd74bae-ff5b-4f58-ab20-0218f820ffff"'::text;


mainid
workexp
count
pg_typeof


"7cd74bae-ff5b-4f58-ab20-0218f820ffff"
[{"id": "c8a5328d-87ba-419d-802f-80b1d940bb68|313384d2-486d-4b7f-ba33-76b1cd696f0a|fd3c41b8-8c15-47e2-a80d-cf3683b2d0da", "level1": "Programming languages", "level2": "Scripting languages", "level3": "TypeScript", "level1_id": "c8a5328d-87ba-419d-802f-80b1d940bb68", "level2_id": "313384d2-486d-4b7f-ba33-76b1cd696f0a", "level3_id": "fd3c41b8-8c15-47e2-a80d-cf3683b2d0da"}, {"id": "84dff39f-2ed6-408c-a188-14cf55a09c5b|e13b48c1-fd0f-4ecc-89de-58e9312b9435|686af7e4-6d58-4148-b227-3bf65ff10273", "level1": "Software frameworks & libraries", "level2": "Frontend frameworks & libraries", "level3": "React", "level1_id": "84dff39f-2ed6-408c-a188-14cf55a09c5b", "level2_id": "e13b48c1-fd0f-4ecc-89de-58e9312b9435", "level3_id": "686af7e4-6d58-4148-b227-3bf65ff10273"}, {"id": "84dff39f-2ed6-408c-a188-14cf55a09c5b|c4e54726-7bd5-44bb-8597-a05eb2272e2b|cda4441f-dba6-495c-9e2e-7429bd5e0465", "level1": "Software frameworks & libraries", "level2": "Backend frameworks & libraries", "level3": "Node.js", "level1_id": "84dff39f-2ed6-408c-a188-14cf55a09c5b", "level2_id": "c4e54726-7bd5-44bb-8597-a05eb2272e2b", "level3_id": "cda4441f-dba6-495c-9e2e-7429bd5e0465"}, {"id": "84dff39f-2ed6-408c-a188-14cf55a09c5b|e13b48c1-fd0f-4ecc-89de-58e9312b9435|fd3c41b8-8c15-47e2-a80d-cf3683b2d0da", "level1": "Software frameworks & libraries", "level2": "Frontend frameworks & libraries", "level3": "TypeScript", "level1_id": "84dff39f-2ed6-408c-a188-14cf55a09c5b", "level2_id": "e13b48c1-fd0f-4ecc-89de-58e9312b9435", "level3_id": "fd3c41b8-8c15-47e2-a80d-cf3683b2d0da"}]
5
jsonb



SELECT 1


--query filter mainid then the inner id.
-- "84dff39f-2ed6-408c-a188-14cf55a09c5b|e13b48c1-fd0f-4ecc-89de-58e9312b9435|fd3c41b8-8c15-47e2-a80d-cf3683b2d0da"
with a as (
SELECT workexp
FROM foo_json,
jsonb_array_elements(work_experience) workexp),
b as(select workexp['id'] mainid,
            jsonb_array_elements(workexp['skills']) last_level,
            count(*) over() from  a),
c as(select
         last_level,
    pg_typeof(mainid) from b
    where mainid::text = '"7cd74bae-ff5b-4f58-ab20-0218f820ffff"'::text)
select *,
       last_level['level1'],
       last_level['level2'],
       last_level['level3'],
       last_level['level1_id'],
       last_level['level2_id'],
       last_level['level3_id']
from c
where
    last_level['id']::text
= '"84dff39f-2ed6-408c-a188-14cf55a09c5b|e13b48c1-fd0f-4ecc-89de-58e9312b9435|fd3c41b8-8c15-47e2-a80d-cf3683b2d0da"'::text;


last_level
pg_typeof
last_level
last_level
last_level
last_level
last_level
last_level


{"id": "84dff39f-2ed6-408c-a188-14cf55a09c5b|e13b48c1-fd0f-4ecc-89de-58e9312b9435|fd3c41b8-8c15-47e2-a80d-cf3683b2d0da", "level1": "Software frameworks & libraries", "level2": "Frontend frameworks & libraries", "level3": "TypeScript", "level1_id": "84dff39f-2ed6-408c-a188-14cf55a09c5b", "level2_id": "e13b48c1-fd0f-4ecc-89de-58e9312b9435", "level3_id": "fd3c41b8-8c15-47e2-a80d-cf3683b2d0da"}
jsonb
"Software frameworks & libraries"
"Frontend frameworks & libraries"
"TypeScript"
"84dff39f-2ed6-408c-a188-14cf55a09c5b"
"e13b48c1-fd0f-4ecc-89de-58e9312b9435"
"fd3c41b8-8c15-47e2-a80d-cf3683b2d0da"



SELECT 1
last_level	last_level	last_level
"c8a5328d-87ba-419d-802f-80b1d940bb68\|313384d2-486d-4b7f-ba33-76b1cd696f0a\|fd3c41b8-8c15-47e2-a80d-cf3683b2d0da"	"Programming languages"	"Scripting languages"
"84dff39f-2ed6-408c-a188-14cf55a09c5b\|e13b48c1-fd0f-4ecc-89de-58e9312b9435\|686af7e4-6d58-4148-b227-3bf65ff10273"	"Software frameworks & libraries"	"Frontend frameworks & libraries"
"84dff39f-2ed6-408c-a188-14cf55a09c5b\|c4e54726-7bd5-44bb-8597-a05eb2272e2b\|cda4441f-dba6-495c-9e2e-7429bd5e0465"	"Software frameworks & libraries"	"Backend frameworks & libraries"
"84dff39f-2ed6-408c-a188-14cf55a09c5b\|e13b48c1-fd0f-4ecc-89de-58e9312b9435\|fd3c41b8-8c15-47e2-a80d-cf3683b2d0da"	"Software frameworks & libraries"	"Frontend frameworks & libraries"
"c8a5328d-87ba-419d-802f-80b1d940bb68\|313384d2-486d-4b7f-ba33-76b1cd696f0a\|012abcd1-3a6a-4803-a47e-42f46b402024"	"Programming languages"	"Scripting languages"
"c8a5328d-87ba-419d-802f-80b1d940bb68\|f45abc59-1e7e-4853-a498-999fcf12d498\|4b266297-6e25-4443-90ec-248bded4225a"	"Programming languages"	"High-level languages"
"c8a5328d-87ba-419d-802f-80b1d940bb68\|f45abc59-1e7e-4853-a498-999fcf12d498\|4b266297-6e25-4443-90ec-248bded4225a"	"Programming languages"	"High-level languages"
"c8a5328d-87ba-419d-802f-80b1d940bb68\|313384d2-486d-4b7f-ba33-76b1cd696f0a\|012abcd1-3a6a-4803-a47e-42f46b402024"	"Programming languages"	"Scripting languages"