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.
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 |