Postgres jsonb Index Format

Question

I'm trying to find the name for these type of indexes. I looked on Google and Postgres documentation and they have nothing on what Value-> operator does. And how its different from a regular: -> SomeInt.

CREATE INDEX "someIndex" ON "me-db"."someTable" USING btree ((((jsondata -> 'SomeInt'::text) -> 'HasValue'::text)))
CREATE INDEX "someIndex2" ON "me-db"."someTable" USING btree ((((jsondata -> 'SomeInt'::text) -> 'Value'::text)))

What are the names for ->HasValue and ->Value operators? How is ->Value different from ->Field.

AlexYes · Accepted Answer · 2020-02-17 18:38:40Z

1

These are the key names in you json structure. If you wrap one with another it means you have a nested json like this (for the above lines):

{"SomeInt": {"HasValue": 1, "Value": 2}}

answered Feb 17, 2020 at 18:38

AlexYes

4,2282 gold badges18 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jn1kk Over a year ago

We had a bug on code that created indexes on the fly. And we have a nullable type, and it created Value, HasValue indexes (reflectively looking at a nullable type) and thats it. We understand this now, thanks!

jjanes · Accepted Answer · 2020-02-17 19:56:48Z

0

Those are called expressional or (more commonly but less correctly) functional indexes.

You are not using ->Value, you are using ->'Value'. Those are different things. The fist would be looking for a column named "value", and using its contents as the name of the key to look for. The second uses the literal string 'Value' as the name of the key to look for.

answered Feb 17, 2020 at 19:56

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Collectives™ on Stack Overflow

Postgres jsonb Index Format

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related