postgresqljsonpath

Flatten values retrieved from any level of a JSON object


I have JSON objects that contain double values under the key value. However, sometimes those are nested, so data structures like this can exist:

[
    {
      "value": 0.1
    },
    {
      "value": {
        "value": 0.3
      }
    }
]

I'm trying to come up with an expression that would return only 0.1 and 0.3. Is this possible? The JSON is stored as JSONB field in a PostgreSQL database, if that makes any difference.


Solution

  • You could use .** JSONPath accessor in a jsonb_path_query() and make sure you're only catching values of .type()=="number" using a ?() filter expression:
    demo at db<>fiddle

    select id,jsonb_path_query(jdata,'strict $.**.value ? (@.type() == "number")')
    from test;
    
    id jsonb_path_query
    1 0.1
    1 0.3