Search Postgresql Archives

Re: help to query json column

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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
> 
> 
> 





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux