Since I posted about the generated fields I’ve learned some interesting new information that I wanted to share as well. There is an interesting detail that may lead to issues.

In my previous example I used the following to map to fields inside the JSON:

price_info->"$.salesprice"

The -> here is the interesting part: There are two variations, being the aforementioned -> and another one ->>.

-> simply extracts the value from the JSON. This is best used to ensure correct types from the data for basically anything but strings. If you have, for instance, an integer value or a boolean in your JSON and you map it to a correctly typed column, you’ll get an error if you use ->> because that will always cast it to a string. Why? I’ll get back to that. For now: If your value is not a string, best use ->.

->> does more than simply extracting the value. It also unquotes the value. This is useful for strings, because in JSON they are always between ". If you use -> on a string value, then you will get that value, plus the quotes. Such as "value". If you use ->> then you will get value. Without quotes. So ->> is the best one to use for string values. However, as mentioned before, this does mean that it will always assume a string value. So if your value is not a string, it will be made a string. If your generated column is not of a string type, this will give you errors about typing.