CODEWITHSUNDEEP
postgresql
maxhallinan
maxhallinan

Reputation: 1339

Why is jsonb_path_query missing values for some rows?

I have this jsonb that I'm trying to flatten and migrate to conversations and tags tables:

{
  id: '1',
  type: 'conversation',
  tags: [
   {
    id: '1',
    type: 'tag',
    name: 'foo'
   }, {
    id: '2',
    type: 'tag',
    name: 'bar'
   },
   {
    id: '3',
    type: 'tag',
    name: 'baz'
   }
  ]
}

But the following query is returning unexpected results:

SELECT 
    jsonb_path_query(payload, '$.id') #>> '{}' conversation_id,
    jsonb_path_query(payload, '$.tags[*].name') tag_name
FROM conversations;

conversation_id | tag_name
1                 foo
                  bar
                  baz

I want the following instead

conversation_id | tag_name
1                 foo
1                 bar
1                 baz

I might be missing something obvious but I'm unclear:

Upvotes: 0

Views: 143

Answers (1)

jjanes
jjanes

Reputation: 44323

This is how multiple set-returning functions in the select-list works since v10. See not starting "Before PostgreSQL 10, putting more than one set-returning function"

Your query can be converted to the simpler:

SELECT 
    payload #>> '{id}' conversation_id,
    jsonb_path_query(payload, '$.tags[*].name') tag_name
FROM conversations;

Upvotes: 1

Related Questions