Re: index on values stored in a json array

Hi Raph,

On Oct 25, 2013, at 5:17 AM, Raphael Bauduin wrote:

> Hi,
>
> I have a table with a column of type json, in which arrays of products are stored in the form:
>
> {products: [ {id : 123 , name ='product1'}, {id: 214, name: 'product 2}], size: 'XL'}
>
> Is is possible to create an index on the row's products' ids?
> I know I have to use GIN index, but I don't know how to collect the product ids out of the array in the create index command.

So with 9.3 this is unfortunately still nontrivial to create an index on an array nested in a JSON object. GIN indexes are not supported on JSON yet. If the ids were not in a nested array, you would be able to create an expression index using "json_extract_path_text" pointing to the ids you want to extract.

However, another approach would be to pull the array out of the JSON data, extract the integer IDs and put them into an integer array, and then put the integer array into a separate column. From there you could create a GIN index on the integer array column.

Best,

Jonathan