phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stephen Sprague <sprag...@gmail.com>
Subject Re: MAP type?
Date Thu, 15 May 2014 16:50:44 GMT
hate to bang on the json drum again but having a json_extract() function
this or any other non-scalar datatype could be implemented using a varchar
datatype.

granted the syntax isn't as clean:

column['key'] vs. json_extract(column,'$.key')

but i think it gets the job done.

presto uses json_extract_scalar() and json_extract() among others. cf.
http://prestodb.io/docs/current/functions/json.html.  This could be used as
a guide, perhaps.




On Wed, May 14, 2014 at 2:30 PM, Rob Roland <rob@simplymeasured.com> wrote:

> Hi all,
>
> I have a use-case that would benefit well from a MAP type, similar to how
> Hive uses a map. I'd like to avoid using a JOIN, due to the amount of rows
> on each side of the JOIN. In Hive's case, you would assign a column family
> to the MAP field.
>
> Essentially, I have an entity that I want to add quite a bit of dynamic
> data that would be unknown at both initial query time and query time. A
> contrived example is below:
>
> I'd like to do something like this:
>
> CREATE TABLE facebook_post (
> id VARCHAR PRIMARY KEY,
> post_body VARCHAR,
>  bitly_links MAP<VARCHAR, VARCHAR>
> );
>
> SELECT * FROM facebook_post;
>
> As opposed to:
>
> CREATE TABLE facebook_post (
> id VARCHAR PRIMARY KEY,
> post_body VARCHAR
> );
>
> CREATE TABLE facebook_post_links (
> facebook_post_id VARCHAR,
> bitly_id VARCHAR,
> expanded_link VARCHAR,
>  CONSTRAINT pk PRIMARY KEY (facebook_post_id, bitly_id)
> );
>
> SELECT * FROM facebook_post AS fp
> LEFT OUTER JOIN facebook_post_links AS fpl ON (fpl.facebook_post_id =
> fp.id);
>
> I realize that a MAP type is outside of the SQL standard, but it would be
> excellent in my use-case. I could accomplish this with an ARRAY type, but
> the expansion of bit.ly links happens as a post-processing step, and
> would probably involve locking, as appending to an ARRAY has to happen
> client-side.
>
> I'd be willing to take a shot at making a MAP type, but I would want to
> make sure this change would be welcomed into mainline Phoenix before
> embarking on something like this.
>
> Thanks,
>
> Rob Roland
>

Mime
View raw message