Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[FEATURE] Add json_append and json_extend functions to PPL #3219

Open
acarbonetto opened this issue Dec 20, 2024 · 0 comments
Open

[FEATURE] Add json_append and json_extend functions to PPL #3219

acarbonetto opened this issue Dec 20, 2024 · 0 comments
Labels
enhancement New feature or request

Comments

@acarbonetto
Copy link
Collaborator

Is your feature request related to a problem?

As part of the RFC to add JSON functions, the json_append and json_extend functions allow users to manipulate the json document by adding elements to a json array. json_extend flattens arrays before appending the values to the json document array.

What solution would you like?

### `JSON_APPEND`

**Description**

`json_append(doc, [path_key, list of values to add]...)`  appends values to end of an array at path within the json document. Return the updated json object. 

**Argument type:** JSON_OBJECT, List<STRING>

**Return type:** JSON_OBJECT

**Note**
Append adds the value to the end of the existing array with the following cases:
  - path is an object value - append is ignored and the value is returned
  - path is an existing array - the value are added to the array's tail
  - path not found - the value are added to the root of the json tree

`json_append` differs from `json_extend` in that it does not flatten arrays before appending them. 

Example:

    os> source=people | eval append = json_append(json('{"teacher":["Alice"],"student":[{"name":"Bob","rank":1},{"name":"Charlie","rank":2}]}'),'student', json('{"name":"Tommy","rank":5}')) | head 1 | fields append
    fetched rows / total rows = 1/1
    +-----------------------------------------------------------------------------------------------------------------------------------+
    | append                                                                                                                            |
    +-----------------------------------------------------------------------------------------------------------------------------------+
    |{"teacher":["Alice"],"student":[{"name":"Bob","rank":1},{"name":"Charlie","rank":2},{"name":"Tommy","rank":5}]}                     |
    +-----------------------------------------------------------------------------------------------------------------------------------+

    os> source=people | eval append = json_append(json('{"teacher":["Alice"],"student":[{"name":"Bob","rank":1},{"name":"Charlie","rank":2}]}`,'teacher', json_array('Tom', 'Walt')) | head 1 | fields append
    fetched rows / total rows = 1/1
    +-----------------------------------------------------------------------------------------------------------------------------------+
    | append                                                                                                                            |
    +-----------------------------------------------------------------------------------------------------------------------------------+
    |{"teacher":["Alice",["Tom","Walt"]],"student":[{"name":"Bob","rank":1},{"name":"Charlie","rank":2}]}                                 |
    +-----------------------------------------------------------------------------------------------------------------------------------+


    os> source=people | eval append = json_append(`{"school":{"teacher":["Alice"],"student":[{"name":"Bob","rank":1},{"name":"Charlie","rank":2}]}}`, 'school.teacher', 'Tom', 'school.teacher', 'Walt') | head 1 | fields append
    fetched rows / total rows = 1/1
    +-------------------------------------------------------------------------------------------------------------------------+
    | append                                                                                                                  |
    +-------------------------------------------------------------------------------------------------------------------------+
    |{"school":{"teacher":["Alice","Tom","Walt"],"student":[{"name":"Bob","rank":1},{"name":"Charlie","rank":2}]}}            |
    +-------------------------------------------------------------------------------------------------------------------------+
### `JSON_EXTEND`

**Description**

`json_extend(doc, [path_key, list of values to add]...)`  extends an array at path within the json document. Return the updated json document. 

**Argument type:** JSON_OBJECT, List<STRING>

**Return type:** JSON_OBJECT

**Note**
Append adds the value to the end of the existing array with the following cases:
  - path is an object value - append is ignored and the value is returned
  - path is an existing array - the value are added to the array's tail
  - path not found - the value are added to the root of the json tree

`json_extend` differs from `json_append` in that it flattens arrays before appending them the json document arrays. 

Example:

    os> source=people | eval extend = json_extend(json('{"teacher":["Alice"],"student":[{"name":"Bob","rank":1},{"name":"Charlie","rank":2}]}'),'student', json('{"name":"Tommy","rank":5}')) | head 1 | fields append
    fetched rows / total rows = 1/1
    +-----------------------------------------------------------------------------------------------------------------------------------+
    | extend                                                                                                                            |
    +-----------------------------------------------------------------------------------------------------------------------------------+
    |{"teacher":["Alice"],"student":[{"name":"Bob","rank":1},{"name":"Charlie","rank":2},{"name":"Tommy","rank":5}]}                     |
    +-----------------------------------------------------------------------------------------------------------------------------------+

    os> source=people | eval extend = json_extend(json('{"teacher":["Alice"],"student":[{"name":"Bob","rank":1},{"name":"Charlie","rank":2}]}`,'teacher', json_array('Tom', 'Walt')) | head 1 | fields append
    fetched rows / total rows = 1/1
    +-----------------------------------------------------------------------------------------------------------------------------------+
    | extend                                                                                                                            |
    +-----------------------------------------------------------------------------------------------------------------------------------+
    |{"teacher":["Alice","Tom","Walt"],"student":[{"name":"Bob","rank":1},{"name":"Charlie","rank":2}]}                                 |
    +-----------------------------------------------------------------------------------------------------------------------------------+

    os> source=people | eval extend = json_extend(`{"school":{"teacher":["Alice"],"student":[{"name":"Bob","rank":1},{"name":"Charlie","rank":2}]}}`, 'school.teacher', 'Tom', 'school.teacher', 'Walt') | head 1 | fields append
    fetched rows / total rows = 1/1
    +-------------------------------------------------------------------------------------------------------------------------+
    | extend                                                                                                                  |
    +-------------------------------------------------------------------------------------------------------------------------+
    |{"school":{"teacher":["Alice","Tom","Walt"],"student":[{"name":"Bob","rank":1},{"name":"Charlie","rank":2}]}}            |
    +-------------------------------------------------------------------------------------------------------------------------+

What alternatives have you considered?

N/A

Do you have any additional context?

opensearch-project/opensearch-spark#780 - PR to add feature to opensearch-spark PPL

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
None yet
Development

No branches or pull requests

1 participant