Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
jsonb handle
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
create table "complains"(complain_id INT primary key, ccmd jsonb); insert into "complains" (complain_id, ccmd) values (1, '[ { "cm_id": 108, "cmd_id": 46304, "cm_name": "category", "cmd_name": "Furniture", "installation_date": "null", "warranty_duration": 0 }, { "cm_id": 109, "cmd_id": 0, "cm_name": "brand", "cmd_name": "", "installation_date": "null", "warranty_duration": 0 }, { "cm_id": 110, "cmd_id": 0, "cm_name": "model", "cmd_name": "", "installation_date": "null", "warranty_duration": 0 } ]'); insert into "complains" (complain_id, ccmd) values (2, '{}'); create table asset_tat(tat_id INT primary key, cmd_id INT); insert into asset_tat (tat_id, cmd_id)values (1, 46304); select o.complain_id, so.* from "complains" o cross join lateral jsonb_array_elements(case jsonb_typeof(ccmd) when 'array' then ccmd else '[]' end) j(suborder11) join asset_tat so on so.cmd_id ::TEXT = j.suborder11 ->> 'cmd_id'
edit mode
|
history
|
discussion