Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ PostgreSQL 9.3.2 Json type of use     - VMWare virtual machine without rebooting way to add virtual disk (Linux)

- Using Linux command line and execute PHP code (Programming)

- ARM assembler instruction debugging method (Programming)

- Android project using the command to create and install the package (Programming)

- Python Django direct implementation of sql statement (Programming)

- Vi (Vim) keyboard map and its basic commands (Linux)

- How do you prevent other users from accessing your home directory in Linux (Linux)

- CUDA (including GPU card driver) installation process under Ubuntu (Linux)

- Install Websphere MB required system rpm package under Linux (Linux)

- How to use the Linux terminal Git commands (Linux)

- Easy to install Ubuntu 15.04 and Ubuntu 15.04 GNOME on Wayland trial (Linux)

- C ++ free store and heap (Programming)

- Pydev installed and configured on the Eclipse (Linux)

- Easy to get hidden administrator account (Linux)

- installation process of Matlab 2012a under Ubuntu 14.04 (Linux)

- Debian (Wheezy) Install Java environment / replace OpenJDK as the SUN JDK (Linux)

- Modify Linux SSH default port 22 in several ways (Linux)

- To configure Samba to share files with Windows under CentOS (Linux)

- OpenDaylight Helium version installed (Linux)

- Zend Studio PHP syntax color scheme to export (Linux)

 
         
  PostgreSQL 9.3.2 Json type of use
     
  Add Date : 2018-11-21      
         
         
         
  First, create a test table


CREATE TABLE job
(
 jobid SERIAL primary key,
 jobdesc json
)


Second, the selectors


-> (Returns an object type)
- >> (Return character)
# >> (Select array type)


Third, select insert update delete records


1. Insert Record


insert into job (jobdesc) values ( '{
  "Jobname": "linux_os_vmstat",
  "Schedule": {
      "Type": { "interval":
          "5m"
      },
      "Start": "now",
      "End": "None"
  },
  "Values": {
      "Event": [ "cpu_r", "cpu_w"],
      "Data": [ "cpu_r"],
      "Threshold": [1,1]
  },
  "Objects": {
      "Wintest1": "cpu"
  }
} ');


insert into job (jobdesc) values ( '{
  "Jobname": "Oracle_tbs_space",
  "Schedule": {
      "Type": { "interval":
          "1d"
      },
      "Start": "now",
      "End": "None"
  },
  "Values": {
      "Event": [ "used"],
      "Data": [ "used"],
      "Threshold": [ "90%"]
  },
  "Objects": {
      "Wintest1": "oradb1"
  }
} ');


2. Select the Record


# Select jobdesc - >> 'jobname' as jobname from job where jobdesc -> 'objects' - >> 'wintest1' like 'oradb1';
    jobname
------------------
oracle_tbs_space
(1 rows)


# Select jobdesc -> 'objects' as objects from job where jobdesc - >> 'jobname' = 'linux_os_vmstat';
        objects
--------------------------
{+
        "Wintest1": "cpu" +
    }
(1 rows)


#select jobdesc -> 'values' # >> '{threshold, 0}' from job where jobdesc - >> 'jobname' = 'oracle_tbs_space';


Array element selection


# Select jobdesc -> 'values' # >> '{event, 0}' as value1 from job where jobdesc - >> 'jobname' = 'linux_os_vmstat';
value1
--------
cpu_r
(1 rows)


# Select jobdesc -> 'values' # >> '{event, 1}' as value2 from job where jobdesc - >> 'jobname' = 'linux_os_vmstat';
value2
--------
cpu_w
(1 rows)

 


2. Update record


#update job set jobdesc = '{
  "Jobname": "linux_os_vmstat",
  "Schedule": {
      "Type": { "interval":
          "5m"
      },
      "Start": "now",
      "End": "None"
  },
  "Values": {
      "Event": [ "cpu_r", "cpu_w"],
      "Data": [ "cpu_r"],
      "Threshold": [1,2]
  },
  "Objects": {
      "Wintest1": "cpu"
  }
} 'Where jobdesc - >>' jobname '=' linux_os_vmstat ';
UPDATE 1
# Select jobdesc -> 'values' # >> '{threshold, 1}' as threshold2 from job
where jobdesc - >> 'jobname' = 'linux_os_vmstat';
threshold2
------------
2
(1 rows)


Must the entire field is updated when you update json type field can not be updated using the specified internal specific value method.


3. Delete Record


# Select * from job;
jobid | jobdesc
------- + ------------------------------------
    3 | {+
      | "Jobname": "oracle_tbs_space", +
      | "Schedule": {+
      | "Type": { "interval": +
      | "1d" +
      |}, +
      | "Start": "now", +
      | "End": "None" +
      |}, +
      | "Values": {+
      | "Event": [ "used"], +
      | "Data": [ "used"], +
      | "Threshold": [ "90%"] +
      |}, +
      | "Objects": {+
      | "Wintest1": "oradb1" +
      |} +
      |}
    4 | {+
      | "Jobname": "linux_os_vmstat", +
      | "Schedule": {+
      | "Type": { "interval": +
      | "5m" +
      |}, +
      | "Start": "now", +
      | "End": "None" +
      |}, +
      | "Values": {+
      | "Event": [ "cpu_r", "cpu_w"], +
      | "Data": [ "cpu_r"], +
      | "Threshold": [1,1] +
      |}, +
      | "Objects": {+
      | "Wintest1": "cpu" +
      |} +
      |}
(2 rows)
#
# Delete from job where jobdesc - >> 'jobname' = 'linux_os_vmstat';
DELETE 1
# Select * from job;
jobid | jobdesc
------- + -----------------------------------
    3 | {+
      | "Jobname": "oracle_tbs_space", +
      | "Schedule": {+
      | "Type": { "interval": +
      | "1d" +
      |}, +
      | "Start": "now", +
      | "End": "None" +
      |}, +
      | "Values": {+
      | "Event": [ "used"], +
      | "Data": [ "used"], +
      | "Threshold": [ "90%"] +
      |}, +
      | "Objects": {+
      | "Wintest1": "oradb1" +
      |} +
      |}
(1 rows)
#
     
         
         
         
  More:      
 
- Why JavaScript basic types can invoke methods (Programming)
- Linux server network penetration testing (Linux)
- To install Spotify in Ubuntu / Mint (Linux)
- To build Spring RestTemplate use HttpClient4 (Programming)
- Compile Android libwebcore.so error occurs when solving (Programming)
- Elasticsearch 2.20 Beginners: aggregation (Server)
- Zabbix installation and configuration process (Server)
- Android start automatically and add and delete a desktop shortcut (Programming)
- Performance issues under CentOS 6.5 VLAN devices (Linux)
- Difference Docker mirror and containers (Server)
- Python programming style (Programming)
- OGG-01496 OGG-01031 Error Resolution (Database)
- MySQL server after an unexpected power outage can not start (Database)
- How to manage and use Logical Volume Management LVM in Ubuntu (Linux)
- PostgreSQL export data dictionary documents (Database)
- MySQL enabled SSD storage (Database)
- Linux system - The understanding cpu load (Linux)
- Linux common network tools: batch scanning of nmap hosting service (Linux)
- The simple multi-threaded Python (Programming)
- Let VMware ESXi virtual switch support VLAN (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.