'Querying json data in POSTGRES and retrieving records where key does not exist
I have a table, Results which has metadata_json as column. I would like to ensure there is no element called _migrationStatus in metadata element and pick only those records. How do i achieve this
Here is a sample json
[{
"id": 4107630,
"relativePath": "losses-hd/6936326/FullEP/",
"absolutePath": "s3a://path",
"resultType": "PLT",
"metadata": {
"type": "FULL_EP",
"_migrationStatus": "SUCCESS"
}
}]
The following query does not work. I want only those records where _migrationStatus does not exist
SELECT * FROM results_metadata WHERE metadata_json ::jsonb ? '_migrationStatus'
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
