Support table-valued functions

[simolus3]

Since the parser optionally supports functions from the json1 module now, maybe we can also add support for table-valued functions. This includes json_each and json_tree in particular. This would let us support queries like

-- assuming user.phone is a json array of phone numbers
SELECT DISTINCT user.name
  FROM user, json_each(user.phone)
 WHERE json_each.value LIKE '704-%';

This is a low priority at the moment.

[knyghtryda]

I'm curious about this enhancement, as I'm currently storing phone numbers, emails, and addresses as a jsons in a column, and am in the process of bringing those values out to their own tables to make manipulating and searching them easier. I would at some point want to be able to do searches over all phone numbers or addresses but currently they just being used within the context of the contact itself. Is there a benefit to storing simple values as a json column vs just giving the values their own table?

[simolus3]

I would probably go with the simplest approach that fits your use case. So as long as you're just using phone numbers/addresses/etc. as a property of a contact that you're not searching on, storing them as json and making use of the data via type converters sounds reasonable. When you want to have complex queries on phone numbers, it's probably best to start normalizing and introduce more tables. You could likely write those complex queries with json_each as well, but proper tables with proper indices will be faster.

[simolus3]

Table-valued functions are supported in the next moor release.