Comparing PostgreSQL JSON Values

Written
  • When comparing a JSON value to a non-JSON value, the easiest way is to use the text operators and coalesce.
    • coalesce(a->>'value', '0')::int > 10
    • This way, if the coalesce works whether the value is null or null::jsonb.

Thanks for reading! If you have any questions or comments, please send me a note on Twitter. And if you enjoyed this, I also have a newsletter where I send out interesting things I read and the occasional nature photo.

You can check out a recent issue, or enter your email below to subscribe.