question: support for data extraction and search for a struct/JSON column #266

chris-rock · 2020-01-02T11:10:52Z

It's a great experimental ent feature to be able to store structs as Json in a database table. I have a few questions regarding this feature:

How to search for data within JSON?
How to extract only specific fields from JSON?
Is there a plan to standardize the abstraction for common use cases?
What should be the syntax to write nested JSON queries?

In the meantime, I believe that ent should have the primitives to allow ent users to write those queries even without out-of-the-box support.

Examples for JSON queries:

-- search sqlite
SELECT *
FROM users, json_each(user_data)
WHERE 
    json_extract(json_each.value, '$.email') LIKE '%criteria%';

-- filter specific values in sqlite
select value, json_extract(user_data, '$.email') from entry where id = '12345';

-- filter specific values in postgres
SELECT user -> 'email' AS user_email
FROM users;

If I read the documentation right, the way to implement search would be a custom predicate.

I am not sure how a custom select statement with only specific fields can be generated. Is the way to go a custom select Select(t1.Columns("name", "age")...) statement? I think the challenge will be to find a good syntax for JSON extraction (specifically for nested fields) and its unclear to me how the mapping to entities should look like.

The text was updated successfully, but these errors were encountered:

a8m · 2020-01-02T14:03:46Z

The "JSON type predicates support" is part of our roadmap (see #46). We didn't come up with the design yet, and I'll be happy to start the discussion/design about it here. However, we plan to finish eager-loading and hooks before start working on this.

How to search for data within JSON?

I thought about generating predicates based on the concrete type (if it's known at compile time).
For example: if the Field type is map[string]T, add predicates like: FieldHasKey. If it's a struct type (like url.URL), add predicates for all struct fields like all regular fields (and use JSON_EXTRACT under the hood).
Note that these are just some ideas I have in my head, and I plan to publish a proper design before start implementing this.

What should be the syntax to write nested JSON queries?

I don't have the answer for this. It's open for discussion.

ernado · 2020-02-27T14:55:30Z

< 8FC6 task-lists disabled sortable>

I propose to support Field type embedding of ent.Schema or something similar to, so it can be generated.

Mickls · 2023-11-02T09:10:44Z

I also encountered the problem of querying json field content when writing crud code. I am very concerned about whether this discussion has a clear answer. Considering that this is already an issue in 2020, is there a more elegant solution that can implement functions like JSON_CONTAINS or JSON_EXTRACT? If so, please reply to me, thank you very much

a8m mentioned this issue Aug 10, 2020

field.json doesn't seem to be generating CRUD searches #664

Closed

masseelch mentioned this issue Feb 3, 2022

Does/How ent support MySQL JSON_SET/JSON_REPLACE/JSON_INSERT? #2273

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

question: support for data extraction and search for a struct/JSON column #266

question: support for data extraction and search for a struct/JSON column #266

question: support for data extraction and search for a struct/JSON column #266

question: support for data extraction and search for a struct/JSON column #266

Comments