> To me, this seems easier to write and understand
Yes, I do agree, once you *know* about ->, and *know* the value is JSON,
then it both makes sense, and it's easy to write. So no, I do not disagree.
I just unsure about *discoverability* and *least surprise*, for the reader which didn't write the SQL (i.e. me after a little while...)
OTOH, I think this below, although verbose, is rather readable, no?
```
select dict.key, entt.key, field.key,
json_extract(field.value, '$.DESC'),
json_extract(field.value, '$.DATA_TYPE'),
json_extract(field.value, '$.DEFAULT_VALUE'),
json_extract(field.value, '$.DISCRIMINANT'),
json_extract(field.value, '$.DEFAULT_QUERY'),
json_extract(field.value, '$.FIXED_VALUE'),
json_extract(field.value, '$.READ_ONLY'),
(select case
when json_extract(field.value, '$.DB_NULLABLE') is not null then null
else ifnull(
json_extract(field.value, '$.REQUIRED'),
json_extract(field.value, '$.DB_NOT_NULL')
)
end
),
json_extract(field.value, '$.CHOICE_VALUE'),
json_extract(field.value, '$.UNIQUE'),
...
from pg,
json_each(pg.js, '$') dict,
json_each(dict.value, '$.entities') entt,
json_each(entt.value, '$.fields') field
```
When I see the above, I wonder how many times we are reparsing the JSON.
Those JSON queries are expensive, it's a larguish JSON *file* (roughly 250 entities, 4'000 fields).
So from my POV, I'd prefer a way to traverse the JSON more directly, and a single time,
to extract the tuples that match a given path. For example, in XPATH, I'd *select* `./entities/fields`
and be able to *match* on the resulting nodes, to extract their value, if this was XML. Can this be done here?
I also have plenty of queries like this (and above)?
```
select attr.key "name",
count(distinct entt.key) as "#entity use"
from pg,
json_each(pg.js, '$') dict,
json_each(dict.value, '$.entities') entt,
json_each(entt.value, '$') attr
group by attr.key
order by attr.key
```
So why only `->` for `json_extract` and not `json_each`?
Why can't I even chain the `->` to implicitly do the joins?
I think an Apple framework did implicit join traversal via a dot or arrow
notation, my ex-boss showed it to me once in an airport.
So could/should `->` also work in table-valued contexts and mean `json_each`?
And `json_each` support multi-join *traversal* implicitly?
I guess I'm saying I'd rather have more speed and ease of use around JSON, not so much syntax sugar.
As usual I probably sound too critical, that's not my intention. I'm more thinking aloud here...