CODEWITHSUNDEEP
jsonpostgresql
Thara
Thara

Reputation: 509

Postgresql table update based on json

I am getting some data in json format. 

jsonData json;

jsonData = '[{
    "id": 1,
        "text": "Materials and varieties",
        "parent": "0"
}, {
    "id": 2,
        "text": "Bricks",
        "parent": "1"
}, {
    "id": "new_1",
        "text": "newitem",
        "parent": "1"
}
]';

Is it possible to do an update for a table field based on id field of jsonData.

I want to do something like

UPDATE item_table SET active = 'N' WHERE
    item_id NOT IN ((jsonData ->>'id')::INTEGER) ;

I also want to restrict jsonData by not allowing ids that match pattern 'new_%'.

I am using Postgres 9.3

Upvotes: 0

Views: 51

Answers (1)

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31153

I might be doing it a bit too complex, but at least you can get the ids this way:

select id::int from (select (json_array_elements('[{
    "id": 1,
        "text": "Materials and varieties",
        "parent": "0"
}, {
    "id": 2,
        "text": "Bricks",
        "parent": "1"
}, {
    "id": "new_1",
        "text": "newitem",
        "parent": "1"
}
]'::json))->>'id' as id) a where id not like 'new_%';

Of course a better check would be to test if the id is actually numeric, but if you know it's either a number or new_, then this also works.

So just add that to your update ... where in ... and you can use the JSON ids.

Upvotes: 2

Related Questions