Re:Using PostgreSQL for JSON Storage
by thisgoodlife on Thursday April 30, 2020 @02:46PM
(#23034534)
I don't like those seemingly arbitrary json operators. ->> #>> ?& @>. IMHO, they'd be easier to understand with proper function names.
They’d have to invent a syntax for infix function calls akin to Haskell’s backticks which other people would complain about.
(I’m happy we don’t have to write queries in yaml, only half joking.)
Other popular databases have named functions for this:
https://www.sqlite.org/json1.html
https://dev.mysql.com/doc/refman/8.0/en/json-function-refere...
https://docs.microsoft.com/en-us/sql/t-sql/functions/json-fu...
(Too bad there was no standard for these.)
Re:Using PostgreSQL for JSON Storage
by eyelidlessness on Thursday April 30, 2020 @09:40PM
(#23038299)
And Postgres has a ton of `JSON_` and `JSONB_` functions, so the weird operators are even more baffling.
Re:Using PostgreSQL for JSON Storage
by eyelidlessness on Thursday April 30, 2020 @09:39PM
(#23038294)
Why have infix anything? Functions have names and they're always the first symbol in a function call. SQL also has a convention of using `PLAIN ENGLISH WORDS` which declaratively describe the result set or operation. The weird JSONB operators are just... way out of place.
I guess PostgreSQL doesn't do everything for me :D
Thanks for catching those and I will be updating the post soon.
Did you get a chance to try the hands on exercise?
That should be #>> (two ">"), not ##> (two "#").
The official docs are really good and they include a number of inline examples showing the difference between the operators: https://www.postgresql.org/docs/current/functions-json.html
The bottom example is missing a colon on the cast as well:
> SELECT json_content FROM mytable WHERE json_content @> '{"status": "Awesome"}':jsonb;
Should be two colons ("::") for an explicit cast: '{"status": "Awesome"}'::jsonb