PostgreSQL – JSON Data Tipis Operations

PostgreSQL – JSON Data Type Operations

It was a big deal when the JSON data type and supporting functions began to be supported with PostgreSQL version 9.2. Today, while other databases are starting to support this data type one by one after PostgreSQL, there is established JSON support in PostgreSQL. JSON, an indispensable language for web application, JavaScript, and REST-based mobile application developers, or as the old-timers would say, the lingua franca (lingua franca lingua franca) in the case of. In version 9.4, support for the jsonb data type, which is the binary version of JSON, was introduced.

JSON Functions and Operators

For all JSON functions and operators https://www.postgresql.org/docs/current/static/functions-json.html You can check the address.

Adding JSON Data

Let's first create a table containing a column with the data type json:

CREATE TABLE family (id serial PRIMARY KEY, profile json);

Then let's add JSON data to this table:

INSERT INTO aile (profil) VALUES ('
{{"ad": "Katip", 
  "fertler": [ 
    {"fert": {"ilişki": "father", "ad": "Hüseyin" }}, 
    {"fert": {"ilişki": "mother", "ad": "Saniye" }}, 
    {"fert": {"ilişki": "child", "ad": "Emrah" }}, 
    {"fert": {"ilişki": "child", "ad": "Sema" }}]}
');

PostgreSQL validates the JSON data before inserting it into the table.

JSON Data Querying

The following query retrieves family members using the json_extract_path, json_array_elements, and json_extract_path_text functions.

I will try to break down the query and explain it:

SELECT 
json_extract_path_text(profil, 'ad') AS aile,
json_extract_path_text(json_array_elements (json_extract_path(profil,'fertler')), 
'fert','ad') As fert 
FROM aile;
--
 aile     | fert
----------+--------- 
Katip     | Hüseyin
Katip     | Saniye
Katip     | Emrah 
Katip     | Sema

SELECT
json_extract_path_text(profil, ‘ad’) AS aile,1
json_extract_path_text( 2 json_array_elements( 3 json_extract_path(profil, ’fertler’) 4 ), ‘fert’,’ad’) AS fert FROM aile;

1. Returns the family name as text
2. Retrieve family member name as text
3. Retrieves array elements as separate JSON objects
4. It brings family members as separate objects

Instead of writing it this way, I find it easier to use function shortcut operators, which probably feels more natural to those who do object-oriented programming. The query above then becomes much simpler like this:

SELECT profile->>'name' As family, 
  json_array_elements((profile->'members')) #>> '{member,name}'::text[] AS member 
FROM families_j;

Returning a JSON Result

row_to_json it is possible to output the selected columns in JSON format using the function.

select row_to_json(words) from words;

Note: PostgreSQL also supports XML like other databases.