I'm trying to find an efficient way to extract specific fields from a Postgres jsonb column.
CREATE TABLE foo (
id integer,
data jsonb
)
"data" contains a row with:
{
"firstname": "bob",
"lastname": "smith,
"tags": ["tag0","tag1"]
}
I want to extract a large number of fields from the data column. This select statement works, but it's cumbersome with large numbers of fields, yields really long SQL statements, and also I don't know if it is traversing the jsonb repeatedly for each column:
SELECT data->>'firstname', data->'tags' FROM foo
I tried this:
SELECT jsonb_path_query(data, '$.[firstname,tags]') FROM foo
but it gave an error message: syntax error, unexpected '['
This syntax is, in fact, correct jsonpath per https://jsonpath.com/, but it appears that Postgres doesn't implement it.
Is there a way to extract jsonb fields efficiently, both in terms of execution speed and compactness of the SQL query command?