Postgres 11

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 data (
    rule_results json
);

INSERT INTO data VALUES
('[{"rule_key":"applicant_not_lived_outside_eu"},{"rule_key":"family_assets_exceed_limit"},{"rule_key":"owned_a_deed"}]
'),
('[]'),
('[{"rule_key":"family_category","details":"apply_with_parents_under_25"}]');

SELECT * FROM data

CREATE TABLE

INSERT 0 3

rule_results

[{"rule_key":"applicant_not_lived_outside_eu"},{"rule_key":"family_assets_exceed_limit"},{"rule_key":"owned_a_deed"}]

[]

[{"rule_key":"family_category","details":"apply_with_parents_under_25"}]

SELECT 3

SELECT 
    value -> 'rule_key'
FROM
    data,
    json_array_elements(rule_results)

?column?

"applicant_not_lived_outside_eu"

"family_assets_exceed_limit"

"owned_a_deed"

"family_category"

SELECT 4

SELECT 
    json_agg(value -> 'rule_key')
FROM
    data,
    json_array_elements(rule_results)
GROUP BY rule_results::text

json_agg

["family_category"]

["applicant_not_lived_outside_eu", "family_assets_exceed_limit", "owned_a_deed"]

SELECT 2