JSONB, Arrays & FTS — Series 3

Preview — 3 of 10 questions

Given data = '{"tags": ["a", "b"]}'::jsonb, whats the difference between `jsonb_set(data, {tags,0}, x)` and `jsonb_insert(data, {tags,0}, x')`?

Ajsonb_set overwrites whatever's already at that path — the result is {"tags": ["x", "b"]}, replacing "a". jsonb_insert instead inserts "x" at that position, shifting existing elements aside — the result is {"tags": ["x", "a", "b"]}, with "a" preserved and shifted right

BThey're identical; both insert "x" as a new element without touching existing ones

Cjsonb_insert only works on object keys, never on array indexes

Djsonb_set always appends to the end of an array, ignoring the path's index

Given data = '{"a": 1, "b": {"c": 2, "d": 3}}'::jsonb, what do data - 'a' and data #- '{b,c}' each produce?

Adata - 'a' removes the top-level key a, producing {"b": {"c": 2, "d": 3}}; data #- '{b,c}' removes the value at the given nested path (an array of path segments), producing {"a": 1, "b": {"d": 3}} — - operates on a single top-level key, #- navigates into nested structure via a path array

BBoth produce a syntax error; jsonb values can only be modified via jsonb_set

C- and #- are exact synonyms, both requiring a full path array as their right-hand operand

D#- deletes the entire object, leaving only {}

All three convert text into a tsquery for full-text search, but they parse their input very differently. What's the key distinction?

AThey're functionally identical; the three names exist purely for historical/backward-compatibility reasons

Bto_tsquery expects already-formatted query syntax (cat & dog, cat | dog, explicit &/|/!/<-> operators) and errors on plain natural-language text with unexpected punctuation; plainto_tsquery takes ordinary text and ANDs together the lexemes it finds, with no operator syntax understood at all; websearch_to_tsquery (the newest) accepts familiar web-search-style syntax directly from end users — quoted phrases, OR, and a leading - for exclusion — without erroring on stray punctuation the way to_tsquery would

COnly websearch_to_tsquery supports English; the other two are language-agnostic

Dplainto_tsquery is deprecated and should never be used in new code

Answer all 10 questions (7 more), see explanations for every answer, and track your score.