![]() ![]() We’ve also used the coalesce function to provide a default value. This coerces the return value as text, which responds appropriately to the IS NULL check. Note that we’re using the -> operator to check for the null. We can use a case expression to bail out in case the field turns out to be a null: SELECT id, coalesce ( case when ( author -> 'address' ) IS NULL then null else ( author -> 'address' -> 'street_name' ) end, 'No street name' ) AS author_street_name FROM books WHERE id = 4 With the behaviour of the -> operator, we are able to do this without an error (it’ll return null): SELECT author -> 'address' -> 'street_name' FROM books where id = 1 īut if we do this, we’ll get an error: SELECT author -> 'address' -> 'street_name' FROM books where id = 4 ERROR : cannot extract element from a scalar Using a CASE expression We’ll use this little table for testing: CREATE TABLE books ( id int, author json ) INSERT INTO books VALUES ( 1, null ), ( 2, '' ) If you can’t make that guarantee though, you’ve got a few other options to navigate safely around the null. The easiest way to avoid this is to make sure you never set a JSON key to null if you’re ever going to treat that key as an object. However, if it encounters a key whose value is actually null. You can chain it on a nested object and it’ll always return null if it encounters a key which doesn’t exist. PostgreSQL’s -> operator is generally pretty good at soaking up null references. Attempting to use one of the JSON operators on a null will result in an error like this: ERROR: cannot extract element from a scalar For example, if you’re trying to query a nested JSON object which contains null values. If you’re using the native JSON support in PostgreSQL you may, from time to time, come across the issue of null safety. ![]()
0 Comments
Leave a Reply. |