6 ms·

How many engineers does it take to make subscripting work?

122 points by aleyan 5y ago

fractal618 5y ago
<2
spaetzleesser 5y ago
why not this? SELECT jsonb_column.key FROM table; UPDATE table SET jsonb_column.key = '"value"';
- loloquwowndueo 5y ago
  Because the dot is used in table.column notation when the column name is ambiguous (in a join). Overloading it to also denote JSON field values would be confusing, the very thing the change mentioned in the article seeks to avoid.
  - mattmanser 5y ago
    It's got nothing to do with ambiguity, you're fundamentally thinking about dot notation wrong. The dot notation is the actual naming convention, not using it is a convenient shortcut. Many professional programmers would chew you out for not using it, being lazy and leaving it off can easily introduce unintended bugs. For example, you add a new column to a table, and boom, half your SQL statements now fail because it's got the same name as an existing column on another table.
gerdesj 5y ago
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 5y 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 5y 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 5y 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 5y ago
      ARRAY[elem1, elem2, …] is a PostgreSQL array literal SQL expression.
    - hans_castorp 5y 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"}'
btown 5y ago
Whenever I'm adding a new table, I almost always try to add an "internal_notes" text field and a "config" JSONB field, no matter how static we think the data definition is going to be. Because at an early stage startup stage, you're going to need to hack some fixes together. And given a choice between needing to QA a database migration vs. just adding {{foo.config.extra_whatever}} in a template, or using it in a single line of logic, the latter is just so much more feasible and easy-to-understand for a same-day bugfix, and you can always move to a dedicated column with a data migration later. Of course, if you're adding this to a few dozen rows, then you might need to jump into SQL to do this at scale. And then you run into the wonderful situations described in the article. I've had to write the following a great many times: config = jsonb_set(coalesce(config, '{}'::jsonb), '{"some","key","path"}', to_jsonb(something::text)) Or this, which is great if you need to set a top-level key to something static; the || operator just mixes the two together similar to Object.assign: config = coalesce(config, '{}'::jsonb) || '{"foo": "bar"}'::jsonb And honestly, the syntax, especially the second one, isn't half bad once you get used to it. That said, the syntax from the article is amazing, and it will go a long way towards people reaching for Postgres even when their data model has a lot of unknowns.
- konschubert 5y ago
  In an early stage startup, why not use an ORM with a built-in migration engine (django migrations, alembic,...) Using jsonb at such a early stage without a strong business reason seems to me like rolling the red carpet for tech debt.
  - debarshri 5y ago
    I absolutely agree with you. It also affects how you can scale a team in early stage. It is super easy to tell the new member to be look up framework specific documentation.
- goto11 5y ago
  Conceptually it seems simpler to add a column to table rather than adding a property to some serialized structure. I wonder if it isn't a question of process and tooling encouraging the wrong thing? At least in other contexts I have seen JSON or XML fields used as a trick to circumvent a heavyweight process for adding new columns. But IMHO the process should be improved rather than circumvented. Some times you need to quickly add a column, but adding a column is also a pretty safe operation. Doing the same thing "one level" up does not improve safety or convenience. In more traditional organizations I have seen the DBA act as a gatekeeper, which lead to developers inventing all kinds of hack to get around the barrier. I can understand the thinking which lead to this, but it is dysfunctional.
SPBS 5y ago
The semantic difference between `jsonb_set` and the new subscripting syntax reminds me of ES6 arrow function situation: a new and improved syntax built seemingly to replace the old one, except there's a slight semantic difference such that you sometimes still have to fall back to the old syntax for the specific effect. Not complaining, but just an additional quirk to have to teach beginners.
tester756 5y ago
it reminds me https://ericlippert.com/2003/10/28/how-many-microsoft-employees-does-it-take-to-change-a-lightbulb/ https://ericlippert.com/2003/10/28/how-many-microsoft-employ...
da39a3ee 5y ago
The work looks awesome. I am really really unconvinced that any teams should be reviewing patches by email like this when we have excellent PR-based workflows and interfaces. I know these people are 1000x better C programmers than me, but I think they are wrong and being unreceptive to improved technologies on this one.

122 points by aleyan 5y ago

fractal618 5y ago

spaetzleesser 5y ago

why not this? SELECT jsonb_column.key FROM table; UPDATE table SET jsonb_column.key = '"value"';

loloquwowndueo 5y ago
Because the dot is used in table.column notation when the column name is ambiguous (in a join). Overloading it to also denote JSON field values would be confusing, the very thing the change mentioned in the article seeks to avoid.
- mattmanser 5y ago
  It's got nothing to do with ambiguity, you're fundamentally thinking about dot notation wrong. The dot notation is the actual naming convention, not using it is a convenient shortcut. Many professional programmers would chew you out for not using it, being lazy and leaving it off can easily introduce unintended bugs. For example, you add a new column to a table, and boom, half your SQL statements now fail because it's got the same name as an existing column on another table.

gerdesj 5y ago

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 5y 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 5y 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 5y 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 5y ago
    ARRAY[elem1, elem2, …] is a PostgreSQL array literal SQL expression.
  - hans_castorp 5y 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"}'

btown 5y ago

Whenever I'm adding a new table, I almost always try to add an "internal_notes" text field and a "config" JSONB field, no matter how static we think the data definition is going to be. Because at an early stage startup stage, you're going to need to hack some fixes together. And given a choice between needing to QA a database migration vs. just adding {{foo.config.extra_whatever}} in a template, or using it in a single line of logic, the latter is just so much more feasible and easy-to-understand for a same-day bugfix, and you can always move to a dedicated column with a data migration later. Of course, if you're adding this to a few dozen rows, then you might need to jump into SQL to do this at scale. And then you run into the wonderful situations described in the article. I've had to write the following a great many times: config = jsonb_set(coalesce(config, '{}'::jsonb), '{"some","key","path"}', to_jsonb(something::text)) Or this, which is great if you need to set a top-level key to something static; the || operator just mixes the two together similar to Object.assign: config = coalesce(config, '{}'::jsonb) || '{"foo": "bar"}'::jsonb And honestly, the syntax, especially the second one, isn't half bad once you get used to it. That said, the syntax from the article is amazing, and it will go a long way towards people reaching for Postgres even when their data model has a lot of unknowns.

konschubert 5y ago
In an early stage startup, why not use an ORM with a built-in migration engine (django migrations, alembic,...) Using jsonb at such a early stage without a strong business reason seems to me like rolling the red carpet for tech debt.
- debarshri 5y ago
  I absolutely agree with you. It also affects how you can scale a team in early stage. It is super easy to tell the new member to be look up framework specific documentation.
goto11 5y ago
Conceptually it seems simpler to add a column to table rather than adding a property to some serialized structure. I wonder if it isn't a question of process and tooling encouraging the wrong thing? At least in other contexts I have seen JSON or XML fields used as a trick to circumvent a heavyweight process for adding new columns. But IMHO the process should be improved rather than circumvented. Some times you need to quickly add a column, but adding a column is also a pretty safe operation. Doing the same thing "one level" up does not improve safety or convenience. In more traditional organizations I have seen the DBA act as a gatekeeper, which lead to developers inventing all kinds of hack to get around the barrier. I can understand the thinking which lead to this, but it is dysfunctional.

SPBS 5y ago

The semantic difference between `jsonb_set` and the new subscripting syntax reminds me of ES6 arrow function situation: a new and improved syntax built seemingly to replace the old one, except there's a slight semantic difference such that you sometimes still have to fall back to the old syntax for the specific effect. Not complaining, but just an additional quirk to have to teach beginners.

tester756 5y ago

it reminds me https://ericlippert.com/2003/10/28/how-many-microsoft-employees-does-it-take-to-change-a-lightbulb/ https://ericlippert.com/2003/10/28/how-many-microsoft-employ...

da39a3ee 5y ago

The work looks awesome. I am really really unconvinced that any teams should be reviewing patches by email like this when we have excellent PR-based workflows and interfaces. I know these people are 1000x better C programmers than me, but I think they are wrong and being unreceptive to improved technologies on this one.