RE: help to query json column

["Charles Clavadetscher" <clavadetscher@xxxxxxxxxxxx>]

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