Postgres find rows where a json attribute has a certain value

user_598596 0
Viewed 112

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'
postgresql
2 Answers

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

user_3303150

If you want the Id and want to see the whole json object or only the relevant outboundConnector's name value, this is the query:

SELECT id ,
       outboundconnector_name
       --,json_workflow
FROM   (
              SELECT id,
                     Jsonb_array_elements(value::jsonb)->'outboundConnector'->>'name' outboundconnector_name ,
                     json_workflow
              FROM   deployed_workflows x,
                     Json_each(x.json_workflow::json) y
              WHERE  KEY = 'outboundWorkflows') inline_view
WHERE  outboundconnector_name ilike '%Push HTTP Single Person%';

Output:

id |  outboundconnector_name
----+--------------------------
  1 | Push HTTP Single Person
  1 | Push HTTP Single Person1
(2 rows)

To get the whole JSON object , just uncomment this piece '--,json_workflow'.

The table on which the query was run:

postgres=# select * from deployed_workflows;
-[ RECORD 1 ]-+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id            | 1
json_workflow | {"name": "Http Pull And Push", "class": "Workflow", "threadcount": 1, "inboundConnector": {"name": "Pull JSON Persons"}, "outboundWorkflows": [{"name": "Push HTTP Single PersonWF", "class": "OutboundWorkflow", "outboundConnector": {"name": "Push HTTP Single Person"}}, {"name": "Push HTTP Single Person1WF", "class": "OutboundWorkflow", "outboundConnector": {"name": "Push HTTP Single Person1"}}]}
-[ RECORD 2 ]-+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id            | 2
json_workflow | {"name": "Http Pull And Push", "class": "Workflow", "threadcount": 1, "inboundConnector": {"name": "Pull JSON Persons"}, "outboundWorkflows": [{"name": "Push HTTP Double PersonWF", "class": "OutboundWorkflow", "outboundConnector": {"name": "Push HTTP Double Person"}}, {"name": "Push HTTP Double Person1WF", "class": "OutboundWorkflow", "outboundConnector": {"name": "Push HTTP Double Person1"}}]}
user_75256750
Related