3 ms·
> Let's get the users last name. SELECT json_content ##> {person, last_name} FROM mytable; > The #> or #> is the JSON path navigator with the difference b
by koolba 7y ago
> Let's get the users last name.
SELECT json_content ##> {person, last_name} FROM mytable;
> The #> or #> is the JSON path navigator with the difference being #> returns JSON and the ##> returns the JSON text value.
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 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
- thisgoodlife 7y agoI don't like those seemingly arbitrary json operators. ->> #>> ?& @>. IMHO, they'd be easier to understand with proper function names.
- baq 7y agoThey’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.)
- wayneftw 7y agoOther popular databases have named functions for this: https://www.sqlite.org/json1.html https://www.sqlite.org/json1.html https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html https://dev.mysql.com/doc/refman/8.0/en/json-function-refere... https://docs.microsoft.com/en-us/sql/t-sql/functions/json-functions-transact-sql?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/t-sql/functions/json-fu... (Too bad there was no standard for these.)
- eyelidlessness 7y agoAnd Postgres has a ton of `JSON_` and `JSONB_` functions, so the weird operators are even more baffling.
- eyelidlessness 7y agoWhy 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.
- spousty 7y agoI 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?
- spousty 7y agoShould be fixed now - thanks!