👀 Views: 276 💬 Answers: 1 📅 Created: 2025-07-27

postgresql jsonb cte SQL

This might be a silly question, but Hey everyone, I'm running into an issue that's driving me crazy. After trying multiple solutions online, I still can't figure this out. I'm running into an scenario while trying to use a Common Table Expression (CTE) with a JSONB field in PostgreSQL 14. The CTE is supposed to aggregate some data and then I want to filter on a specific key within a JSONB column. However, I'm getting the following behavior: ``` behavior: 42883: function jsonb_each_text(jsonb) does not exist ``` Here's a simplified version of my query: ```sql WITH json_data AS ( SELECT id, data FROM my_table ) SELECT id, key, value FROM json_data, jsonb_each_text(data) AS kv(key, value) WHERE key = 'specific_key'; ``` I thought `jsonb_each_text()` was the right function to use for unpacking the JSONB data. To troubleshoot, I confirmed that the `data` column is indeed of type JSONB by running: ```sql SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'my_table'; ``` This confirmed that `data` is JSONB as expected. I also verified that I'm connected to the correct database and the function exists in the current schema. However, I still need to understand why PostgreSQL need to find the function. I even tried using `jsonb_each()` instead, but I received a similar behavior. What could be causing this scenario? Am I missing something in the way I'm referencing the function or is there a better approach to achieve the desired outcome? Any help would be greatly appreciated! I'm working on a service that needs to handle this. This is for a desktop app running on Debian. The stack includes Sql and several other technologies. Thanks for your help in advance!