sqljsonduckdb

Tips for extracting data from a JSON column in DuckDb

I'm looking for a duckdb function similar to redshift's JSON_EXTRACT_PATH_TEXT(). If I have a column that is a VARCHAR version of a JSON, I see that I can convert from the string to JSON by CAST(column_name as JSON), but how do I get at the attributes?

Solution

  • You can use the json extraction functions, like so:

    select (' { "family": "anatidae", "species": [ "duck", "goose", "swan", null ] }')->'species'->>0;

    See https://duckdb.org/docs/extensions/json#json-extraction-functions for more information