3 ms·

I'm trying to understand this post, so I turned the initial "problematic" SQL into this: SELECT j->'k' FROM t; UPDATE t SET j = f(j, '{"k"}', '"v"'); ...

I'm trying to understand this post, so I turned the initial "problematic" SQL into this: SELECT j->'k' FROM t; UPDATE t SET j = f(j, '{"k"}', '"v"'); ... and the provided solution into this: SELECT j['k'] FROM t; UPDATE t SET j['k'] = '"v"'; I think I understand what a binary JSON column might be but by stripping variables and function names down to the minimum, both of these constructs look a bit heavy on the quotes and brackets (parenthesis.) There are squiggly brackets and square ones, single and double quotes. A lack of a colon is clearly an oversight. I think I understand now: the function "jsonb_set on a "'@thing@'" is to become a magic result by inference with enough syntax on a variable. I think I may have some way to go before I achieve enlightenment 8)

klodolph 6y ago
So, the extra quotes may seem like a lot, but I think in practice these are going to be parameterized queries... so something like this (in Python): cursor.execute( """UPDATE t SET j['k'] = ?;""", (json.dumps(newvalue),)) For transmitting JSON to the database server, it makes sense that the JSON would be serialized as a string, because that's the only standardized serialization format for JSON anyway. When you use parameterized queries, the parameters don't get quotes.
minitech 6y ago
> A lack of a colon is clearly an oversight. You mean {"k"}? That's PostgreSQL array syntax, not JSON. It's correct. ('{"k"}' could also be written ARRAY['k'].)
- quelltext 6y ago
  What does ARRAY['k'] mean? EDIT: Okay, so I looked up what {'k'} means, i.e. it's an array with one element 'k'. And in the article's function it's the "path" to the key to be set. Still not sure if ARRAY['k'] is a thing, though. I could only find this sort of syntax described in the context of definition, e.g. ARRAY[4] for an array of length 4.
  - minitech 6y ago
    ARRAY[elem1, elem2, …] is a PostgreSQL array literal SQL expression.
  - hans_castorp 6y ago
    > Still not sure if ARRAY['k'] is a thing, though. Yes it is. It's an alternative way of writing an array. Typically easier for e.g. text values as the quoting follows the normal SQL rules, e.g. array['foo"bar', 'bla'] vs '{"foo\"bar", "bar"}'

klodolph 6y ago

So, the extra quotes may seem like a lot, but I think in practice these are going to be parameterized queries... so something like this (in Python): cursor.execute( """UPDATE t SET j['k'] = ?;""", (json.dumps(newvalue),)) For transmitting JSON to the database server, it makes sense that the JSON would be serialized as a string, because that's the only standardized serialization format for JSON anyway. When you use parameterized queries, the parameters don't get quotes.

minitech 6y ago

> A lack of a colon is clearly an oversight. You mean {"k"}? That's PostgreSQL array syntax, not JSON. It's correct. ('{"k"}' could also be written ARRAY['k'].)

quelltext 6y ago
What does ARRAY['k'] mean? EDIT: Okay, so I looked up what {'k'} means, i.e. it's an array with one element 'k'. And in the article's function it's the "path" to the key to be set. Still not sure if ARRAY['k'] is a thing, though. I could only find this sort of syntax described in the context of definition, e.g. ARRAY[4] for an array of length 4.
- minitech 6y ago
  ARRAY[elem1, elem2, …] is a PostgreSQL array literal SQL expression.
- hans_castorp 6y ago
  > Still not sure if ARRAY['k'] is a thing, though. Yes it is. It's an alternative way of writing an array. Typically easier for e.g. text values as the quoting follows the normal SQL rules, e.g. array['foo"bar', 'bla'] vs '{"foo\"bar", "bar"}'