5 ms·
Another exciting feature in PG14 is the new JSONB syntax[0], which makes it easy to update deep JSON values - UPDATE table SET some_jsonb_column['person']['b
by e1g 5y ago
Another exciting feature in PG14 is the new JSONB syntax[0], which makes it easy to update deep JSON values -
UPDATE table SET some_jsonb_column['person']['bio']['age'] = '99';
[0] https://erthalion.info/2021/03/03/subscripting/ https://erthalion.info/2021/03/03/subscripting/
- xfalcox 5y agoWow is this for real? That is such a big quality of life change! Happy to see it!
- megous 5y agoNot much different from some_jsonb#>>'{some,path}' and once you add the need to convert out of jsonb to text, you'll not be saving any characters either. At least for queries. For updates, it looks nice I guess.
- zdragnar 5y agoI think the difference is familiarity. It shouldn't matter so much, but when you don't use one language as much as you do other languages, it becomes that much harder to remember unfamiliar syntaxes and grammars, and easier to confuse similar looking operations with each other.
- megous 5y agoIn that case this does not help. SELECT json['a']; will not return the value of the string in {"a":"ble"} (like it does in Javascript), but a JSON encoding of that string, so '"ble"'. You'll still not be able to do simple comparisons like `SELECT json_col['a'] = some_text_col;` Superficial familiarity, but it still behaves differently than you expect. Is there even a function that would convert JSON encoded "string" to text it represents in postgresql? I didn't find it. So all you can do is `SELECT json_col['a'] = some_text_col::jsonb;` and hope for the best (that string encodings will match) or use the old syntax with ->> or #>>.
- derefr 5y ago> Is there even a function that would convert JSON encoded "string" to text it represents in postgresql? I didn't find it. Oddly, no, there's no specific function for taking a root-level scalar JSON term (like '"foo"'::jsonb), and extracting said scalar to its equivalent native Postgres type. You can still do it (for extracting to text, at least), but you have to use a 'vacuous' path-navigation to accomplish it, so it's extremely clumsy, and wastes the potential of the new syntax: SELECT '"foo"'::jsonb #>> (ARRAY[]::text[]);
- megous 5y agoThanks for the idea. This is a bit shorter: SELECT '"foo"'::jsonb #>>'{}'; But yeah: SELECT jsonb_col['prop1']#>>'{}' FROM ...; looks a bit meh. And custom right unary operators are on the way out, so one can't even create one for this use case. Anyway, for fun: create function deref_jsonb(jsonb) returns text as $$ begin return $1#>>'{}'; end $$ language plpgsql; CREATE OPERATOR # ( leftarg = jsonb, function = deref_jsonb ); select '"sdfasdf"'::jsonb #; select jsonb_col['a']# FROM somewhere; :)
- derefr 5y agoYou could also take advantage of PG's function/field equivalence: -- equivalent to deref_jsonb('"sdfasdf"'::jsonb) select ('"sdfasdf"'::jsonb).deref_jsonb; (I'd suggest naming the function "inner_text", for familiarity to JS devs :P)
- megous 5y agoOh my. :) There's always some quirky little thing to learn about PostgreSQL, lol.
- megous 5y agoDownvoters could instead provide a way to get a decoded text of a property with this new syntax, like it's possible with #>>. That would be more useful.
- derefr 5y agoWouldn't one big difference be that with this syntax, you can use bind-parameters / joined row-tuple fields / expression values as jsonpath keys? ETA: no, actually, I was wrong — #>> takes text[], so you can already pass it an ARRAY[] literal containing expressions. It's just all the examples in the PG docs that use the IO syntax to represent the path, and then rely on an implicit cast to text[].
- da39a3ee 5y agoWould you be able to give a bit of context for the limitations of the new syntax that you’re pointing out? Could they be overcome (and if so why did it ship like this) or are they inevitable?
- megous 5y agoI don't think it's a limitation, it's just by design. a['b'] is equivalent to a->'b' not to a->>'b', otherwise deep references (a['b']['c']) would not work because first a['b'] would return text and not jsonb value.
- GordonS 5y agoGods, but this is fabulous! JSONB capabilities in Postgres are amazing, but the syntax is really annoying - for example, I'm forever mixing up `->` and `->>`. This new syntax feels far more intuitive.
- topicseed 5y agoConstantly have to google up the JSONB wuery syntax, it's just too confusing to me, although it is indeed powerful. Glad to this this super intuitive and familiar syntax added. Will make writing these updates a lot lot lot easier. Not even close!
- roenxi 5y agoPostgres is bowing to the inevitable, JSON support is too much in demand. But this is going to be a classic example of bad design. Databases are a bad place to be storing JSON, which is a good interface and a bad storage standard. It is pretty easy to see how JSON will play out: some bright young coder will use JSON because it is easier, then over the course of 12 months discover the benefits of a constrained schema, and then have a table-in-a-table JSON column. It isn't so out there to think that ongoing calls for JSON support will lead Postgres to re-implement tables in JSON. We've already got people trying to build indexes on fields inside a JSON field. This is needless complexity engineered by people who insist on relearning schemas from scratch, badly, rather than trusting the database people who say "you need to be explicit about the schema, do data modelling up front".
- ilikepi 5y agoThe native JSON data type was introduced with PG 9.2 in 2012.
- roenxi 5y agoI know. And it isn't a new argument either, I for one have been making it for years.
- deleted 5y ago[deleted]
- deleted 5y ago[deleted]
- paulddraper 5y ago> Postgres is bowing to the inevitable I think PostgreSQL has always been very pragmatic. It's supported JSON natively since 9.2 (Sep 2012). > Databases are a bad place to be storing JSON You're right that "mature" features and projects have a very good understand of the schema. But not everything is that. Suppose I want to collect info from the Github API about a bunch of repos. I can just store the entire JSON response in a table and then query it at my leisure. There's also something to be said for contiguous access. Joining tons of little records together has performance problems. Composite types and arrays can also fill this void, but they both have their own usability quirks.
- bionhoward 5y agolove it. time to bake ramda.sql