search

opensearch-project / sql

Query your data using familiar SQL or intuitive Piped Processing Language (PPL)
https://opensearch.org/docs/latest/search-plugins/sql/index/
Apache License 2.0
120 stars 139 forks source link

[FEATURE]Add PPL `JSON` extended functions support #3028

Open YANG-DB opened 2 months ago

YANG-DB commented 2 months ago

Is your feature request related to a problem? We need to enhance OpenSearch PPL (Piped Processing Language) by adding support for JSON-based operations. This extension will provide users with powerful tools to manipulate and analyze JSON data within PPL queries.

Proposed New Functions

  1. json_object(): Creates a JSON object from existing key/value pairs. 
    ... | eval person = json_object("name", "John", "age", 30)
  2. json(): Evaluates whether a value can be parsed as JSON. Returns the value if valid, null otherwise. 
    ... | eval result = json('{"name":"John","age":30}')
  3. json_append(): Appends elements to the contents of a valid JSON object. 
    ... | eval person = json_object("name", "John"), extended_person = json_append(person, "city", "New York")
  4. json_array(): Creates a JSON array using a list of values. 
    ... | eval json_list = json_array("apple", "banana", "cherry")
  5. json_array_to_mv(): Maps the elements of a JSON array to a multivalued field. 
    ... | eval json_list = json_array("apple", "banana", "cherry"), mv_field = json_array_to_mv(json_list)
  6. json_delete(): Removes one or more keys and their corresponding values from the specified JSON object. 
    ... | eval person = json_object("name", "John", "age", 30, "city", "New York"), person_no_age = json_delete(person, "age")
  7. json_extend(): Extends the contents of a valid JSON object with the values of an array. 
    ... | eval person = json_object("name", "John", "age", 30), extended_person = json_extend(person, json_array("city", "New York"))
  8. json_extract(): Returns either a JSON array or a native type value from a field and zero or more paths. 
    ... | eval data = json('{"person": {"name": "John", "age": 30}}'), name = json_extract(data, "person.name")
  9. json_keys(): Returns the keys from the key-value pairs in a JSON object as a JSON array. 
    ... | eval person = json_object("name", "John", "age", 30), keys = json_keys(person)
  10. json_set(): Inserts or overwrites values for a JSON node with provided values and returns an updated JSON object. 
    ... | eval person = json_object("name", "John"), updated_person = json_set(person, "age", 30)
  11. json_valid(): Evaluates whether a JSON object uses valid JSON syntax and returns TRUE or FALSE. 
    ... | eval is_valid = json_valid('{"name":"John","age":30}')
  12. all(): Iterates over JSON array values and returns true if every value matches the provided predicate. 
    ... | eval json_list = json_array(1, 2, 3), all_match = all(json_list, val -> val != 0)
  13. any(): Iterates over JSON array values and returns true if any value matches the provided predicate. 
    ... | eval json_list = json_array(1, 2, 3), any_match = any(json_list, val -> val == 0)
  14. filter(): Iterates over JSON array values and performs an operation on each value. 
    ... | eval json_list = json_array(1, 2, 3, 4), filtered_list = filter(json_list, val -> val % 2 == 0)
  15. map(): Iterates over JSON array values and performs an operation on each value. 
    ... | eval json_list = json_array(1, 2, 3), mapped_list = map(json_list, val -> val * 2)
  16. reduce(): Iterates over a JSON array in a field or a literal array and performs an accumulation operation. 
    ... | eval json_list = json_array(1, 2, 3), sum = reduce(json_list, 0, (acc, val) -> acc + val)

Technical Considerations