JSON Path - XPath Benzeri Bir Arama Dili

Giriş

Açıklaması şöyle

JSON Path is a powerful tool for searching and manipulating a JSON object in SQL using JavaScript-like syntax:

- Dot (.) is used for member access

- Square brackets ("[]") are used for array access

- SQL/JSON arrays are 0-indexed, unlike regular SQL arrays that start from 1

Açıklaması şöyle

The jsonpath type implements support for the JSONPath standard in PostgreSQL to efficiently query JSON data. It provides similar functionality to XPath for XML. It’s a very powerful language, and allows you to traverse json structures level by level and retrieve arbitrary sub-items using multiple assessor operators and nested filter expressions. You can have strict or lax error handling. If lax, the default, then the path engine adapts the queried data to the path, otherwise with strict, an exception occurs. 

Built-In Functions

1. size

Açıklaması şöyle

JSONPath also includes powerful built-in functions like size() to find the length of arrays. 

Örnek

Elimizde şöyle bir tablo olsun

CREATE TABLE LunchOrders(student_id INT, orders JSONB); 

Bu tabloya veri girelim. Json verisi tek tırnak içindedir.

INSERT INTO LunchOrders VALUES(100, '{
  "order_date": "2020-12-11",
  "order_details": {
    "cost": 4.25,
    "entree": ["pizza"],
    "sides": ["apple", "fries"],
    "snacks": ["chips"]}
  }'      
);

INSERT INTO LunchOrders VALUES(100, '{
  "order_date": "2020-12-12",
  "order_details": {
    "cost": 4.89,
    "entree": ["hamburger"],
    "sides": ["apple", "salad"],
    "snacks": ["cookie"]}
  }'      
);

Şöyle yaparız

SELECT * FROM lunchorders WHERE orders @@ '$.order_details.snacks.size() > 0';

2. Comparison Without Type Casting

JSON Path ile şöyle yaparız

SELECT * FROM lunchorders WHERE orders @@ '$.order_details.cost > 4.50';

JSON Operators kullansaydık şöyle yapmak gerekirdi. ->> ile Filtering Query kullanılıyor

SELECT * FROM lunchorders WHERE  (orders -> 'order_details' ->> 'cost')::numeric > 4.50;