Note that the function will return the found object (unless it’s null) as a json type, so you’ll usually need to cast it to text or another suitable type when you use it. the author field) and then one parameter for each nested field. To use it, you pass it the object you’re working with (i.e. CREATE OR REPLACE FUNCTION json_fetch ( object json, variadic nodes text ) RETURNS json AS $$ DECLARE result json : = object k text BEGIN foreach k in array nodes loop if ( result -> k ) is null then result : = null exit end if result : = result -> k end loop return result END $$ LANGUAGE plpgsql The function below, json_fetch, is a simple implementation which will safely traverse and return a nested object without errors. Using a functionĪlternatively, you may find it more convenient (and less verbose) to create your own function. There are some caveats around this with regards to constant expressions, but these shouldn’t apply in most cases. This technique will work for any level of nesting, because when used in this way the case expression essentially ‘short circuits’ and will stop evaulating when it has a match. 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. The same query with COALESCE will be: SELECT courseinstanceid, COALESCE(AVG(grade), 0) AS averagegradeincourse FROM studentcourses GROUP BY courseinstanceid And now we can get the result table as: EXAMPLE: Using COALESCE with SUM () function This one is a similar example. 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 COALESCE () function comes with help in replacing a values. 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. WHERE CASE WHEN x 0 THEN y/x > 1.If you’re using the native JSON support in PostgreSQL you may, from time to time, come across the issue of null safety. For example, this is a possible way of avoiding a division-by-zero failure: The example above can be written using the simple CASE syntax:Ī CASE expression does not evaluate any subexpressions that are not needed to determine the result. This is similar to the switch statement in C. If no match is found, the result of the ELSE clause (or a null value) is returned. The first expression is computed, then compared to each of the value expressions in the WHEN clauses until one is found that is equal to it. There is a “ simple” form of CASE expression that is a variant of the general form above: The data types of all the result expressions must be convertible to a single output type. If the ELSE clause is omitted and no condition is true, the result is null. If no WHEN condition yields true, the value of the CASE expression is the result of the ELSE clause. If the condition's result is not true, any subsequent WHEN clauses are examined in the same manner. If the condition's result is true, the value of the CASE expression is the result that follows the condition, and the remainder of the CASE expression is not processed. Each condition is an expression that returns a boolean result. The SQL CASE expression is a generic conditional expression, similar to if/else statements in other programming languages:ĬASE clauses can be used wherever an expression is valid.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |