3 ms·
Can I index fields within a JSON document in PostreSQL? Can I query them? I.e. can I do something like SELECT json_field FROM data WHERE json_field.age > 15 ?
by gibybo 13y ago
Can I index fields within a JSON document in PostreSQL? Can I query them?
I.e. can I do something like SELECT json_field FROM data WHERE json_field.age > 15 ?
- andreypopp 13y agoYes, this is what index scan performance metrics is about — PostgreSQL outperforms MongoDB here.
- itodd 13y agoThis is how you do it in 9.2 using PLV8. http://people.planetpostgresql.org/andrew/index.php?/archives/249-Using-PLV8-to-index-JSON.html http://people.planetpostgresql.org/andrew/index.php?/archive... For 9.3, see more info at http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-json-operators/ http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-...
- chrisfarms 13y agoyes. See the operators at: http://www.postgresql.org/docs/9.3/static/functions-json.html http://www.postgresql.org/docs/9.3/static/functions-json.htm...
- rpedela 13y agoThat query would be this: SELECT json_field FROM data WHERE json_field->'age' > 15 Part of the performance increase for hstore is improvements for GIN indexes, and according to the author can be applied to json. So yes you can use indexes on your hstore or json documents.
- jeffasinger 13y agoYes, for a project I'm working on, I had a JSON array of objects that each had a key called name. I have fast indexed queries that return any rows that have an object with a given name in it, and for full text search on that name along with a few other things.