help to query json column

From: Arup Rakshit <aruprakshit1987(at)outlook(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: help to query json column
Date: 2018-03-07 04:40:36
Message-ID: 03342D6C-F36F-474E-A5E7-BF07175649DF@outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rene Romero Benavides 2018-03-07 05:43:13 Re: Help troubleshooting SubtransControlLock problems
Previous Message Andres Freund 2018-03-07 03:29:52 Re: dirty_ratio & dirty_background_ratio settings with huge memory