I have a JSONB column with the following values:
{
"collections": {
"drafts": [
{
"availabilities": [],
"languageCodes": [],
"languages": []
}
],
"items": [
{
"languageCodes": [],
"languages": []
},
{
"languageCodes": [],
"languages": []
}
]
}
}
I want to delete all of the languages elements using a wildcard JSON path. I can delete one using the ‘#-‘ as follows:
select
'
{
"collections": {
"drafts": [
{
"availabilities": [],
"languageCodes": [],
"languages": []
}
],
"items": [
{
"languageCodes": [],
"languages": []
},
{
"languageCodes": [],
"languages": []
}
]
}
}
'::jsonb
#-
'{"collections", "items", "0", "languages"}'
I can also find all of the relavent elements with the following using the ** wildcard:
select jsonb_path_query(
'
{
"collections": {
"drafts": [
{
"availabilities": [],
"languageCodes": [],
"languages": []
},
{
"availabilities": [],
"languageCodes": [],
"languages": []
}
],
"items": [
{
"languageCodes": [],
"languages": []
},
{
"languageCodes": [],
"languages": []
}
]
}
}
','$.**.languages')
However, there doesn’t seem to be a way to delete those elements with one statement.
I have tried using wildcards in the first query, but no combination seems to work. I believe there is probably a way of doing this using jsonb_each(), deleting elements and repacking the original JSONB, but I am wondering if there is something less convoluted.