0

I have a jsonb column in a table with the following model:

{
  "framework": {
    "name": "framework name",
    "sections": [
      {
        "controls": [
          {
            "name": "control name",
            "accounts": [
              {
                "rules": [
                  {
                    "id": 841,
                    "name": "rule name",
                    "entities": [
                      {
                        "id": "entity id",
                        ...
                      }
                    ]
                  }
                ]
              }
            ]
          }
        ]
      }
    ]
  }
}

I have a query that requires traversing the above-nested structure and perform filter based on specific values. For that purpose I have used cross join lateral multiple times on the extracted values of the jsonb_array_elements() function. Following is the sample sql query.

SELECT
    r.rule_sets_id AS ruleSetId,
    ARRAY_AGG(distinct CAST(account->>'accountId' AS text)) AS accountIdsInJsonArray,
    COUNT(distinct control ->> 'name') AS enabledControls,
    COUNT(distinct rule ->> 'id') AS enabledRules
FROM reports r
CROSS JOIN LATERAL jsonb_array_elements(result->'framework'->'sections') AS section
CROSS JOIN LATERAL jsonb_array_elements(section->'controls') AS control
CROSS JOIN LATERAL jsonb_array_elements(control->'accounts') AS account
CROSS JOIN LATERAL jsonb_array_elements(account->'rules') AS rule
CROSS JOIN LATERAL jsonb_array_elements(rule->'entities') AS entity
INNER JOIN(
    SELECT rule_sets_id, max(modified_on) modified_on
    FROM reports
    WHERE workspace_id = :workspaceId
    AND status = 'COMPLETED'
    GROUP BY rule_sets_id
) r1 ON r.modified_on = r1.modified_on AND r.rule_sets_id = r1.rule_sets_id
WHERE r.workspace_id = :workspaceId AND r.status = 'COMPLETED' 
and account ->> 'accountId' in ('123456789012') 
and account ->> 'dataSource' in ('AWS')
GROUP BY r.rule_sets_id;

Now for the above query I want to enhance the performance of the same by creating required indexes for the jsonb column. Also, there is currently index on columns workspace_id, status, rule_sets_id on the table reports.

Following is the link where I have shared the explain analyze output of the above sql query: https://explain.dalibo.com/plan/66139bc286gbh19c

What can I do to improve performance?

4
  • 1
    "What can I do to improve performance?" - Normalize the JSON into real columns in the database so they can be indexed properly, FWIW.
    – J.D.
    Commented Jun 29, 2023 at 12:06
  • It is already very fast. How much faster do you need it to be?
    – jjanes
    Commented Jun 29, 2023 at 13:32
  • Are all account->>'accountId' guaranteed to be unique for every control ->> 'name'? If so you can remove the DISTINCT from the aggregates and instead aggregate on two levels. Same for rule ->> 'id' are there always unique values in different accounts? Commented Jun 29, 2023 at 15:47
  • Have a look at the @> operator. Commented Jun 30, 2023 at 5:01

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.