Issue: Native support for json_group_array() and json_group_object()
Author: brandond
State: closed
Created: 2018-10-18 18:06:16

Ref: https://www.sqlite.org/json1.html#jgrouparray

Right now I'm using these via `fn`, but I have to manually load the resulting JSON. What's the best way to have these come out as native Python lists or dicts?

Example of current usage:
```python
name, messages = (User.select(User.name,
                              fn.json_group_array(Tweet.message))
                      .join(Tweet)
                      .where(User.id == 123)
                      .scalar(as_tuple=True))
messages = json.loads(messages)
```

FWIW, what I really want in this case is probably something closer to Postgres's `array_agg`, but postgres also has `json_agg`, `json_object_agg`, etc that have their own use case with regards to returning dicts. Also, I'm pretty tied to using sqlite.

I've made a patch which somewhat addresses this issue... The following explanation applies to Peewee <= 3.7.1 (up through commit aa2d6f51dc2f50e4):

Because these are aggregate functions, they do not easily lend themselves to introspection or a sensible API. We wouldn't make "group_array" a method on a `JSONField`, for instance, because it's an aggregate and operates outside the scope of a single column. In your snippet there's nothing to indicate that the returned value is a json-serialized string other than the function name.

Similarly, when reading the result value from the db cursor, we don't have an obvious indication that the results are a json-serialized string other than the function name itself. To see how peewee translates the raw data from a cursor into the appropriate data-types, check out `BaseModelCursorWrapper._initialize_columns()`. In fact, depending on the data-type being grouped, its possible that Peewee could attempt (incorrectly) to convert the serialized values. If you change your example to group the Tweet's "id" field you will get an error.

```python
name, messages = (User.select(User.name,
                              fn.json_group_array(Tweet.id))
                      .join(Tweet)
                      .where(User.id == 123)
                      .scalar(as_tuple=True))
```

This will raise an error in peewee <= 3.7.1 -- "invalid literal for int..." -- because Peewee uses a heuristic to try and normalize the values returned by aggregates -- which is, it looks at the first parameter in the function call and, if its a Field object, will assume that the result of the aggregate returns a compatible data-type. To avoid this, you need to:

```python
name, messages = (User.select(User.name,
                              fn.json_group_array(Tweet.id).coerce(False))
                      .join(Tweet)
                      .where(User.id == 123)
                      .scalar(as_tuple=True))
```

---------------------

Back to your question -- because the column you are aggregating into a json value is not necessarily a JSONField (and in fact is probably not) -- there's nothing to indicate to peewee that the value returned by the db is a json string.

I've made some changes to the internals, however:

1. When inferring a conversion for a SQL function, ignore TypeError / ValueError that is raised when applying conversion. (e.g., calling int(), which is Tweet.id's conversion function, on the result of a group_concat).
2. Make it possible for a function to explicitly declare a conversion. This makes it possible to specify a custom conversion function on a one-off basis.

Example of 2, which should address your problem:

```python
name, messages = (User.select(User.name,
                              fn.json_group_array(Tweet.message).python_value(json.loads))
                      .join(Tweet)
                      .where(User.id == 123)
                      .scalar(as_tuple=True))
```

ecfa98c9f2d5d6b30f238d97928662d909f769e2

I was thinking of full extension support like `remove()`, `length()` etc but that works!
http://docs.peewee-orm.com/en/latest/peewee/sqlite_ext.html#JSONPath.remove

FYI, looks like there's an issue with the `remove()` docstring; it claims to use the `json_type` function.

Right, but it makes no sense calling json_group_xxx from the context of a json field or path (?).