JS Problems geeksforgeeks Exercises and Solutions for Beginners

How To Query JSON Object with Unknown Keys in PostgreSQL [All Method]️

The step-by-step guide on this page will show you How To Query JSON Object with Unknown Keys in PostgreSQL. Error found! Why this could be happening? know and Learn everything.
Question: What is the best solution for this problem? Answer: This blog code can help you solve errors How To Query JSON Object with Unknown Keys in PostgreSQL. Question: What are the reasons for this code mistake and how can it be fixed? Answer: You can find a solution by following the advice in this blog.

TL;DR

Use jsonb_object_keys().

select col->jsonb_object_keys(col) from t;

Not Quite TL;DR

Suppose I have a jsonb column that stores other JSON objects with unknown keys for whatever reason. It could be because they’re user-generated, or maybe they’re timestamps.

Let’s go with timestamps.

{
    "0": {
        "id": "4d3",
        "class": 1
    },
    "120": { 
        "id": "5a7",
        "class": 2
    },
    "240": { 
        "id": "3b8",
        "class": 3
    }
}

We want to grab the class field of each object in column col of table t.

select col->jsonb_object_keys(col)->>'class' from t;

Let’s try this with this standalone WITH query.

with t(col) as (values('{
    "0": {
        "id": "4d3",
        "class": 1
    },
    "120": { 
        "id": "5a7",
        "class": 2
    },
    "240": { 
        "id": "3b8",
        "class": 3
    }
}'::jsonb))
select col->jsonb_object_keys(col)->>'class' from t;
?column?
1
2
3

View on DB Fiddle


Now you learned, How you can use & How To Query JSON Object with Unknown Keys in PostgreSQL.
Final Note: Try to Avoid this type of mistake(error) in future!

Related Articles

Leave a Reply

Your email address will not be published.

Back to top button