Update: Here's a demonstration of the performance improvements on a table of 1,000,000 rabbits where each rabbit likes two foods and 10% of them like carrots: d=# - Postgres 9. Assuming the sometable table has a JSON column named customer, this query will make PostgreSQL examine each row’s document, and return those rows where the name key is equal to Joe. Of course, you probably don't have time for that as a full-time rabbit keeper. Select info->'name' from rabbits where info->'food' ? 'carrots' The id column is the primary key column that identifies the order.
#POSTGRES JSON QUERY CONTAINS CODE#
You can even index the ? query on the "food" key if you switch to the jsonb type instead: alter table rabbits alter info type jsonb using info::jsonb Ĭreate index on rabbits using gin ((info->'food')) Code language: SQL (Structured Query Language) (sql) The orders table consists of two columns. You need extract the value as text using the -> operator, only then can you cast it back to a json or jsonb value. Here's how you can query your JSON column in PostgreSQL: - Give me params.name (text) from the events table select params->'name' from events - Find only events with a specific name select from events where params->'name' 'Click Button' - Give me the first index of a JSON array select params->ids->0 from events - Find users where. As of PostgreSQL 9.4, you can use the ? operator: select info->'name' from rabbits where (info->'food')::jsonb ? 'carrots'
0 kommentar(er)
