Playing with jsonpath and postgresql

In MHS, filters are implemented as a list of couple ["key", "value"].

While managing MHS filters, I need to remove the “microorganism_family” from all user filters, e.g.:

[
    ["microorganism_family", "staaur;staga;acibau"],
    ["device", "WASPLAB;MICROSCAN;BRUKER"],
    ["test", "emoaer;tvag"],
]

must became:

[
    ["device", "WASPLAB;MICROSCAN;BRUKER"],
    ["test", "emoaer;tvag"],
]

JSONPATH

In last versions of postgresql there’s a new feature called ‘jsonpath’ that enables to navigate through json(b) objects.

Jsonpath syntax could be exploited through some jsonpath functions, and jsnonb_path_query_array really fits our needs: we can use it to itarate through all filter parameters, and discarding the unwanted ‘microorganism_family’.

The solution is really clean and concise:

update
    frontend_filter
set
    data = jsonb_path_query_array(
        data,
        '$ ? (@[0] <> "microorganism_family")'
    )::jsonb;

Very nice.