PostgreSQL

PostgreSQL offers powerful capabilities for working with JSON data, which can be especially useful in modern applications that handle semi-structured data. JSON data types in PostgreSQL allow you to store and query JSON documents directly within your database. This is particularly beneficial when dealing with dynamic or flexible data structures. In this section, we'll explore how to perform basic SELECT queries involving JSON data in PostgreSQL.

1. Retrieving JSON Data from a Table

To start with, let’s assume you have a table named transactions that stores transaction details in a JSON column named details.

CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    details JSONB
);

To select and view the JSON data from the details column, you can use the following query:

SELECT details
FROM transactions;

2. Accessing JSON Object Keys

To retrieve specific keys from a JSON object, you can use the -> operator to get JSON objects and the ->> operator to get JSON values as text.

1. Retrieve a JSON Object Field:

   SELECT details->'amount' AS amount
   FROM transactions;
   

   This retrieves the amount field from the details column.

2. Retrieve a JSON Field as Text:

   SELECT details->>'amount' AS amount
   FROM transactions;
   

   This retrieves the amount field as text.

   3. Filtering Rows Based on JSON Values

   You can filter rows based on JSON values using the ->> operator combined with a WHERE clause.

   1. Filter by JSON Field Value:

      SELECT *
      FROM transactions
      WHERE details->>'status' = 'completed';
      

      This query selects transactions where the status field in the JSON data is 'completed'.

   2. Filter by Nested JSON Fields:

      SELECT *
      FROM transactions
      WHERE details->'payment'->>'method' = 'credit_card';
      

      This query selects transactions where the nested JSON field payment.method is 'credit_card'.

   4. Aggregating JSON Data

   You can also aggregate JSON data using PostgreSQL’s JSON functions.

   1. Aggregate JSON Objects:

      SELECT json_agg(details) AS all_details
      FROM transactions;
      

      This aggregates all JSON objects from the details column into a JSON array.

   2. Count JSON Objects Matching Criteria:

      SELECT COUNT(*)
      FROM transactions
      WHERE details->>'status' = 'pending';
      

      This counts the number of transactions with a status of 'pending'.

      Using these techniques, you can effectively query and manipulate JSON data within your PostgreSQL database, enhancing your ability to work with complex and dynamic data structures.

   3. To gain complete access, login with gmail or outlook, no need of signup. click here

      

3. TEST CODE

   To select JSON data from an PostgreSQL table and extract specific fields, use the following query:

   SELECT client_id, first_name, last_name,
   complete_address,
   JSON_VALUE(complete_address, '$.address1') AS address1,
   JSON_VALUE(complete_address, '$.County') AS county,
   JSON_UNQUOTE(JSON_VALUE(complete_address, '$."PO BOX"')) AS po_box
   FROM org_client
   WHERE complete_address IS NOT NULL;

   Try it now

   To select JSON data from an PostgreSQL table and extract specific fields, use the following query:

   SELECT client_id, first_name, last_name,
   JSON_VALUE(complete_address, '$.address1') AS address1,
   JSON_VALUE(complete_address, '$.County') AS county
   FROM org_client
   WHERE JSON_VALUE(complete_address, '$."PO BOX"') = '432';

   Try it now

   To select JSON data from an PostgreSQL table and extract specific fields, use the following query:

   SELECT client_id, first_name, last_name,
   complete_address,
   JSON_VALUE(complete_address, '$.address1') AS address1,
   JSON_VALUE(complete_address, '$.County') AS county
   FROM org_client
   WHERE JSON_VALUE(complete_address, '$."PO BOX"') = '432';

   Try it now

   In PostgreSQL, you can use the `JSON_OBJECT` function to convert tabular data into JSON format. Here's an example query:

   SELECT JSON_OBJECT('client_id' VALUE client_id,
   'info' VALUE JSON_OBJECT(
   'name' VALUE first_name,
   'surname' VALUE last_name
   ),
   'birth_year' VALUE birth_year
   ) AS json_result
   FROM org_client
   WHERE client_id = 1;

   Try it now

   SELECT JSON DATA

   The following SQL queries demonstrate how to extract key-value pairs from a specified JSON column. Additionally, these queries illustrate the utilization of JSON key-value pairs within the WHERE clause.

   RAW DATA WITH JSON DATA TYPE

   

   MySQL QUERY

   
   SELECT
       client_id, first_name, last_name,complete_address,
       complete_address -> '$.address1',
       complete_address ->> '$.County',
       JSON_UNQUOTE(complete_address -> '$."PO BOX"')
   FROM org_client
   where complete_address IS NOT NULL;
   
   

   POSTGRESQL QUERY

   
   SELECT
       client_id, first_name, last_name,complete_address -> 'address1' AS address1,
       complete_address ->> 'County' AS county
   FROM org_client
   where complete_address ->> 'PO BOX' = '432';
   
   

   MS SQL SERVER AND ORACLE QUERY

   
   SELECT
       client_id, first_name, last_name, complete_address,
       JSON_VALUE(complete_address, '$.address1') AS address1,
       JSON_VALUE(complete_address, '$.County') AS county
   FROM org_client
   where JSON_VALUE(complete_address, '$."PO BOX"') = '432';
   
   

   QUERY DATA MAPPING

   

   QUERY OUT PUT