JSON, JSONB questions

From: Tom C <barteri(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: JSON, JSONB questions
Date: 2016-09-29 02:07:52
Message-ID: CACjqFnqGYmsxVutLEmwUVuNYvF+jcv4qu=gW1793Syk=EBp0vA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I'm new to Postgres so forgive me if these are dumb question.

I'm trying to understanding why you would use JSON over JSONB. From what I
have seen so far, JSONB doesn't take that much more space than JSON and
offers better indexing and search capabilities than plain JSON data type.
Is it really just about preserving whitespace and structure? If that was
the case then why bother having the JSON data type at all? Why not just
stick with varchar

Second question is around indexing and searching on data types of JSONB. I
understand that you can create indexes for specific fields within the JSON.
Is there a performance penalty when adding indexes to specific fields in
large JSON payload? For example, let's say I have a very complex nested
JSON payload stored in a JSONB column. All I'm interested in indexing is a
collection array within the JSON payload. Will it be more efficient if I
break out the array into a separate JSONB column?

Third and final question is how do you create an index into for an array
element within the JSONB structure?

Thanks.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Merlin Moncure 2016-09-29 13:31:37 Re: JSON, JSONB questions
Previous Message amul sul 2016-09-21 09:10:29 Re: SELECT FOR UPDATE returns zero rows