Thread: help to query json column

help to query json column

From

Arup Rakshit

Date:

07 March 2018, 10:40:36

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><pathd=\"M 310 96 310 96 308 93 308 87
30886 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
92436 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
114246 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
108609 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
99148 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?

RE: help to query json column

From

"Charles Clavadetscher"

Date:

07 March 2018, 12:57:52

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

Re: help to query json column

From

Arup Rakshit

Date:

07 March 2018, 16:27:30

Thanks Charles. that worked.


> On Mar 7, 2018, at 12:27 PM, Charles Clavadetscher <clavadetscher@swisspug.org> wrote:
> 
> 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
> 
> 
>