RE: help to query json column - Mailing list pgsql-general
From | Charles Clavadetscher |
---|---|
Subject | RE: help to query json column |
Date | |
Msg-id | 07b701d3b5e1$a2fc5030$e8f4f090$@swisspug.org Whole thread Raw |
In response to | help to query json column (Arup Rakshit <aruprakshit1987@outlook.com>) |
Responses |
Re: help to query json column
|
List | pgsql-general |
Hello > -----Original Message----- > From: Arup Rakshit [mailto:aruprakshit1987@outlook.com] > Sent: Mittwoch, 7. März 2018 05:41 > To: pgsql-general@lists.postgresql.org > Subject: help to query json column > > Hi, > > I would like to select only rows where signature has a non null value. My json looks like : > > { > "carInspection": { > "damages": [ > { > "x": 68.670309653916, > "y": 44.08014571949, > "errors": [ > { > "code": "BR", > "description": "Gebrochen" > }, > { > "code": "F", > "description": "Reifen platt" > } > ] > }, > { > "x": 40.8014571949, > "y": 50.273224043716, > "errors": [ > { > "code": "BR", > "description": "Gebrochen" > }, > { > "code": "F", > "description": "Reifen platt" > } > ] > }, > { > "x": 48.269581056466, > "y": 37.340619307832, > "errors": [ > { > "code": "F", > "description": "Reifen platt" > } > ] > }, > { > "x": 49.180327868852, > "y": 15.482695810565, > "errors": [ > { > "code": "F", > "description": "Reifen platt" > } > ] > } > ], > "layoutURL": "default", > "signature1": "<svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 1000 200\">\n<defs><style > type=\"text\/css\"><![CDATA[\npath{stroke: black;stroke-linecap:round;stroke- > width:4;fill:none;}\n]]><\/style><\/defs><g><path d=\"M 310 96 310 96 308 93 308 87 308 86 309 86 314 86 325 88 > 340 92 374 102 379 103 377 102 372 100 364 95 356 91 356 90 357 90 358 90 365 90 380 90 397 92 436 105 460 121 > 464 135 458 142 443 148 423 152 386 152 328 151 277 141 236 129 201 114 186 110 189 111 199 112 215 114 246 116 > 270 119 289 121 295 123 295 123 296 123 296 123 298 124 302 127 314 131 330 135 368 135 405 134 462 127 526 108 > 609 74 641 55 651 34 643 24 621 8 588 -13 553 -33 457 -58 363 -57 278 -31 213 13 164 66 148 99 148 102 148 102 > 148 99 148 92 148 81 148 68 148 66 148 65\"><\/path><\/g><\/svg>", > "signature2": null > } > } > > The table names is inspections, and the column name is *custom_data*. Can anyone help me how to do this? SELECT * FROM inspections WHERE custom_data->'carInspection'->>'signature1' IS NOT NULL; Or SELECT * FROM inspections WHERE custom_data->'carInspection'->>'signature2' IS NOT NULL; depending on which of the signature fields you want to check. Regards Charles
pgsql-general by date: