Thanks Charles. that worked. > On Mar 7, 2018, at 12:27 PM, Charles Clavadetscher <clavadetscher@xxxxxxxxxxxx> wrote: > > Hello > >> -----Original Message----- >> From: Arup Rakshit [mailto:aruprakshit1987@xxxxxxxxxxx] >> Sent: Mittwoch, 7. März 2018 05:41 >> To: pgsql-general@xxxxxxxxxxxxxxxxxxxx >> 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 > > >
