I have a nullable JSON column in a PostgreSQL table. Data stored in the column is an array with key/value objects which have to be converted into records at some point. It’s a job for json_to_recordset, which takes a JSON array as input and returns the records.
1 2 3 4 5 6 7 8 9 10 11 12 | create table people ( properties json);insert into people values (null), ('[{"key": "a"}, {"key": "b"}]');select * from people;select keyfrom people, json_to_recordset(properties) as properties(key text); |
But JSON also allows ‘null’ as value. And I don’t mean it allows null values because it’s a nullable column, it actually allows the string ‘null’, which is recognized as a null JSON value.
1 | insert into people values ('null'); |
The value of the properties column in the new row is not null, so now json_to_recordset will complain about being passed a scalar.
Be careful when updating a JSON column, make sure you really set it to null if you want no value.