Re: help to query json column

Thanks Charles. that worked.

> On Mar 7, 2018, at 12:27 PM, Charles Clavadetscher <clavadetscher(at)swisspug(dot)org> wrote:
>
> Hello
>
>> -----Original Message-----
>> From: Arup Rakshit [mailto:aruprakshit1987(at)outlook(dot)com]
>> Sent: Mittwoch, 7. März 2018 05:41
>> To: pgsql-general(at)lists(dot)postgresql(dot)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
>
>
>