Postgres find rows where a json attribute has a certain value-CodePudding

I have a column in my postgres table where I store json values. It is a text column. The json looks like (shortenend)

{
"class": "Workflow",
"outboundWorkflows": [
    {
        "class": "OutboundWorkflow",
        "outboundConnector": {
            "name": "Push HTTP Single Person"
        },
        "name": "Push HTTP Single PersonWF"
    },
    {
        "class": "OutboundWorkflow",
        "outboundConnector": {
            "name": "Push HTTP Single Person1"
        },
        "name": "Push HTTP Single Person1WF"
    }
],
"threadcount": 1,
"inboundConnector": {
    "name": "Pull JSON Persons"
},
"name": "Http Pull And Push"
}

Now I want to get all the rows in the table, where outboundConnector.name has for example the value "Push HTTP Single Person". It should work case insensitive. So I it should also work for "push http single PERSON".

I am new to the JSON functions in postgres.

My first attempt:

select json_extract_path_text(json_workflow::json, 'name') from deployed_workflow;

I get all the names in the json. I just want the outbound connector names. And I don't know if I can use this in the where clause also.

So I want all the names in the attribute outboundConnector->name:

Push HTTP Single Person

Push HTTP Single Person1

from the query.

If it is possible, I just want to ids of the rows where a outboundConnector.name matches

So something like:

Select id from deployed_workflows where json_workflow::json -> outboundConnector-> name ilike 'Push HTTP Single Person'

Best regards

CodePudding user response：

If you only want the id or the complete row, you can use a JSON path expression if you are using Postgres 12 or later.

select id
from deployed_workflows 
where json_workflow @@ '$.outboundWorkflows[*].outboundConnector.name like_regex "Push HTTP Single Person"'

This assumes that json_workflow is a jsonb column (which it should be). If it's not, you need to cast it to jsonb

Online example