Interesting. Being able to output the results of a query as JSON will be useful when running something like Node.js. I can't shake my feeling that something like this doesn't belong in the SQL statement, though- choice of output format seems like it's something connecting libraries ought to do.
That said, having seen 'core type' I instantly imagined being able to query based on JSON properties, which doesn't appear to be the case. Not surprising, because it would be a huge amount of work.. but it's nice to imagine.
(before anyone says anything- yes, I know NoSQL exists. But a hybrid solution using Postgres would be very interesting)
It's a key-value store that allows querying, which is what I think you are lamenting in your comment. Here's a bit more about how to query and index with hstore: http://lwn.net/Articles/406385/. It's pretty simple and doesn't have anywhere near the number of querying possibilities that MongoDB has, but it can be used for on-the-fly column names (similar to JSON). You can only query on the root node's children, unlike the open-ended possibilities in NoSQL.
I'm (quite happily) tied to Postgres because I'm using PostGIS, but the ability to add freeform data to a location would be ideal. Looks like I may already have a solution here.
@scorpioxy -- I can't reply to you directly, but you've been [dead] for a few months now. I see you've posted quality comments, but you posted what could be perceived as a spam post 138 days ago and so you got marked as a spambot. But you're clearly not. Might want to clear that up, but for now, you're dead when you probably ought to not be.
I'm building a 'taxi tracker' app, for want of a better term- gives you estimated trip times, allows you to share the journey with other people so they know where you are.
That part doesn't require PostGIS as such, but it's for an NYC city government app competition, so we have all sort of city datasets to use. I've used PostGIS to help make custom map tiles (preview at https://twitter.com/#!/taxonomyapp/status/149565007384940545), highlight the outline of the building you're heading to... all sorts. It's been a fantastic learning exercise.
I don't know exactly what your GIS needs are, but it might be worth seeing if MongoDB's geospatial indexing meets them if you are interested in querying free-form data.
What would be interesting would be an extension to the SQL parser that supports JSONPath in SQL queries. like a WHERE JSON_COLUMN = some JSONPath query. Where it would act like a WHERE IN clause and could use a JSON style query syntax. I see no need to create a whole new construct when a valid one is available at this point. The two could be blended and it would create a very powerful way to query JSON data types in a relational system.
That's not going to help when you need to restrict your query with JSON, though; the select list is processed after the whole FROM clause, including JOINs, so you'd be producing the whole table and then throwing away whatever didn't match.
In general, a few core limitations of the current optimizer - lack of LATERAL key among them - make it impossible to create a PostgreSQL function that blends well with SQL, AFAICT.
> select uid from users wehere jsonpath('$.country', address) = 'CH'
PostgreSQL does support functional indexes, so you can easily make this use indexes and thus be fast enough for real use (it's a bad example - I'd probably put the address into an addresses table, but it's enough to illustrate the point)
Comments
Interesting. Being able to output the results of a query as JSON will be useful when running something like Node.js. I can't shake my feeling that something like this doesn't belong in the SQL statement, though- choice of output format seems like it's something connecting libraries ought to do.
That said, having seen 'core type' I instantly imagined being able to query based on JSON properties, which doesn't appear to be the case. Not surprising, because it would be a huge amount of work.. but it's nice to imagine.
(before anyone says anything- yes, I know NoSQL exists. But a hybrid solution using Postgres would be very interesting)
You might be interested in postgresql's hstore: http://www.postgresql.org/docs/9.0/static/hstore.html
It's a key-value store that allows querying, which is what I think you are lamenting in your comment. Here's a bit more about how to query and index with hstore: http://lwn.net/Articles/406385/. It's pretty simple and doesn't have anywhere near the number of querying possibilities that MongoDB has, but it can be used for on-the-fly column names (similar to JSON). You can only query on the root node's children, unlike the open-ended possibilities in NoSQL.
Oh, that is very intersting, thanks!
I'm (quite happily) tied to Postgres because I'm using PostGIS, but the ability to add freeform data to a location would be ideal. Looks like I may already have a solution here.
@scorpioxy -- I can't reply to you directly, but you've been [dead] for a few months now. I see you've posted quality comments, but you posted what could be perceived as a spam post 138 days ago and so you got marked as a spambot. But you're clearly not. Might want to clear that up, but for now, you're dead when you probably ought to not be.
With hstore and psycopg, you'd gain a lot of flexibility that's very similar to another NoSql store. Psycopg does the mapping to/from python dicts.
It's not without a learning curve, but I found it helpful when I hit shortcomings in Mongo's design.
Cool, PostGIS! What are you building? I worked as GIS engineer in 2010. PostGIS is pretty cool technology.
I'm building a 'taxi tracker' app, for want of a better term- gives you estimated trip times, allows you to share the journey with other people so they know where you are.
That part doesn't require PostGIS as such, but it's for an NYC city government app competition, so we have all sort of city datasets to use. I've used PostGIS to help make custom map tiles (preview at https://twitter.com/#!/taxonomyapp/status/149565007384940545), highlight the outline of the building you're heading to... all sorts. It's been a fantastic learning exercise.
I don't know exactly what your GIS needs are, but it might be worth seeing if MongoDB's geospatial indexing meets them if you are interested in querying free-form data.
http://www.mongodb.org/display/DOCS/Geospatial+Indexing http://www.mongodb.org/display/DOCS/Geospatial+Haystack+Inde...
As someone who is building a similar app, for a similar competition (but in Boston):
Don't combine your data structures / stores. Use the store / structure that is most appropriate for the given task. Memory is cheap vs compute time.
Hint: many can be only in memory stores that periodically sync to a backing store for updates.
What would be interesting would be an extension to the SQL parser that supports JSONPath in SQL queries. like a WHERE JSON_COLUMN = some JSONPath query. Where it would act like a WHERE IN clause and could use a JSON style query syntax. I see no need to create a whole new construct when a valid one is available at this point. The two could be blended and it would create a very powerful way to query JSON data types in a relational system.
You wouldn't even need an extension to the SQL parser - you could just expose a SQL function to do the JSONPath query. e.g.
That's not going to help when you need to restrict your query with JSON, though; the select list is processed after the whole FROM clause, including JOINs, so you'd be producing the whole table and then throwing away whatever didn't match.
In general, a few core limitations of the current optimizer - lack of LATERAL key among them - make it impossible to create a PostgreSQL function that blends well with SQL, AFAICT.
> select uid from users wehere jsonpath('$.country', address) = 'CH'
PostgreSQL does support functional indexes, so you can easily make this use indexes and thus be fast enough for real use (it's a bad example - I'd probably put the address into an addresses table, but it's enough to illustrate the point)