Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table rule_definition(definition_id ,design) as values
('some id','{
    "objectVersion": 0,
    "design": [
        {
            "setNumber": true,
            "objectVersion": 0,
            "type": "RULE_PART",
            "number": 0,
            "operator": "EQUAL",
            "left": {
                "objectVersion": 0,
                "type": "RESOLVED",
                "value": "94068ff0-e73e-4f99-9f66-7025bf14ffb4.Ce6188690-7495-4bd0-a9c6-91ef98acb64a",
                "resolverType": "FORM",
                "_type": "RulePartOperand"
            },
            "right": {
                "objectVersion": 0,
                "type": "LITERAL",
                "value": "N",
                "resolverType": null,
                "_type": "RulePartOperand"
            },
            "combine": "AND",
            "subParts": [],
            "_type": "RulePart"
        },
        {
            "setNumber": true,
            "objectVersion": 0,
            "type": "RULE_PART",
            "number": 1,
            "operator": "EQUAL",
            "left": {
                "objectVersion": 0,
                "type": "RESOLVED",
                "value": "94068ff0-e73e-4f99-9f66-7025bf14ffb4.C292d1bbe-cfc0-4736-a164-70882714cbf0",
                "resolverType": "FORM",
                "_type": "RulePartOperand"
            },
            "right": {
                "objectVersion": 0,
                "type": "LITERAL",
                "value": "Y",
                "resolverType": null,
                "_type": "RulePartOperand"
            },
            "combine": "OR",
            "subParts": [],
            "_type": "RulePart"
        }
    ]
}'::jsonb);

SELECT 1


select json_array_elements(design::json -> 'design') 
from public.rule_definition where  definition_id = 'some id'


json_array_elements


{"left": {"type": "RESOLVED", "_type": "RulePartOperand", "value": "94068ff0-e73e-4f99-9f66-7025bf14ffb4.Ce6188690-7495-4bd0-a9c6-91ef98acb64a", "resolverType": "FORM", "objectVersion": 0}, "type": "RULE_PART", "_type": "RulePart", "right": {"type": "LITERAL", "_type": "RulePartOperand", "value": "N", "resolverType": null, "objectVersion": 0}, "number": 0, "combine": "AND", "operator": "EQUAL", "subParts": [], "setNumber": true, "objectVersion": 0}

{"left": {"type": "RESOLVED", "_type": "RulePartOperand", "value": "94068ff0-e73e-4f99-9f66-7025bf14ffb4.C292d1bbe-cfc0-4736-a164-70882714cbf0", "resolverType": "FORM", "objectVersion": 0}, "type": "RULE_PART", "_type": "RulePart", "right": {"type": "LITERAL", "_type": "RulePartOperand", "value": "Y", "resolverType": null, "objectVersion": 0}, "number": 1, "combine": "OR", "operator": "EQUAL", "subParts": [], "setNumber": true, "objectVersion": 0}



SELECT 2


select json_array_elements(design::json -> 'design')->'left'->'value'
from public.rule_definition where  definition_id = 'some id'


?column?


"94068ff0-e73e-4f99-9f66-7025bf14ffb4.Ce6188690-7495-4bd0-a9c6-91ef98acb64a"

"94068ff0-e73e-4f99-9f66-7025bf14ffb4.C292d1bbe-cfc0-4736-a164-70882714cbf0"



SELECT 2


select jsonb_path_query(design::jsonb, '$.design[*].left.value')
from public.rule_definition where  definition_id = 'some id'


jsonb_path_query


"94068ff0-e73e-4f99-9f66-7025bf14ffb4.Ce6188690-7495-4bd0-a9c6-91ef98acb64a"

"94068ff0-e73e-4f99-9f66-7025bf14ffb4.C292d1bbe-cfc0-4736-a164-70882714cbf0"



SELECT 2