node.js and postgresql UPDATE nested JSON key

Question

I have a table called "json" in my database, with 2 columns: "id" and "data"

Only one row is stored inside it at the moment, having 1 as id and a JSON structure as data:

{
    "elements": {
        "nodes": [
            {
                "data": {
                    "id": "n0",
                    "name": "Name here",
                    "color": "#FFFFFF"
                }
            },
            {
                "bob": "hello"
            }
        ]
    }
}

I need to update a key of the json: "Name here" has to become "updated"

This is what I tried:

db.query("UPDATE json SET $1 WHERE data->'elements'->'nodes'->0->'data'->'name'=$2", ['updated', 'Name here'])

but I get an error:

syntax error at or near "'updated'"

@tadman I just changed it to "mydata", but still same error... — neoDev, Feb 03 '17 at 05:51
Does a simpler version of this query succeed? Might have to do some reductive exploration here. — tadman, Feb 03 '17 at 05:55
@tadman this works for example: `db.query("select data->'elements'->'nodes'->0->'data'->'name' from json where id = 1")`, and returns `[ anonymous { '?column?': 'Name here' } ]` — neoDev, Feb 03 '17 at 05:55
Are you sure your finally formatted query is exactly what you think it is? Add event [query](http://vitaly-t.github.io/pg-promise/global.html#event:query) handler to see the exact queries being executed, or just use [pg-monitor](https://github.com/vitaly-t/pg-monitor). — vitaly-t, Feb 03 '17 at 14:45

score 0 · Answer 1 · answered Feb 03 '17 at 06:01

0

When using the Postgres JSON navigators it's important to terminate your chain with the text retrieval navigator ->> if you want to do comparisons like that:

UPDATE json SET $1 WHERE data->'elements'->'nodes'->0->'data'->>'name'=$2

That should permit comparing text to text instead of json.

I think you might also be able to use #>> to dig the whole way down in one shot:

UPDATE json SET $1 WHERE data#>>'{elements,nodes,0,data,name}'=$2

answered Feb 03 '17 at 06:01

tadman

208,517
23
234
262

I get the same error: `syntax error at or near "'updated'"` for both of your examples – neoDev Feb 03 '17 at 06:04
[In this post](http://stackoverflow.com/a/26704636/5638869) I read that postgres updates the whole row even if a change only a little part of it, as JSON in PostgreSQL is intended to store data that do not need to be manipulated. So at this point, if I want to continue in my intent, I could get the JSON (as I shown above), elaborate it, and then send to the database. Does it make sense? – neoDev Feb 03 '17 at 06:28
If you're trying to reassign part of your JSON structure that might not be the syntax to do it. Are you using Postgres 9.6? – tadman Feb 03 '17 at 06:33
What about `jsonb_set`? It's likely in-place editing is only applicable to the more efficient JSONB column type. That's the same as JSON but it's always compacted into a minimal, canonical form, ignoring any initial layout. – tadman Feb 03 '17 at 06:38
I will try `jsonb_set`. However I'm using version 9.6.1 – neoDev Feb 03 '17 at 06:49

node.js and postgresql UPDATE nested JSON key

1 Answers1