Comment by gerdesj | Hacker News Reader

Comment by gerdesj | Hacker News Reader

Parent Full thread

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)

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.

> 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'].)

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.

> 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"}'

ARRAY[elem1, elem2, …] is a PostgreSQL array literal SQL expression.

Reply on news.ycombinator.com