Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise

I am experimenting with the new JSON/JSONB objects in the latest(9.4) PostgreSQL. First, I will show you my test table:

CREATE TABLE "JSONtest" (
    data jsonb
);
COPY "JSONtest" (data) FROM stdin;
{"id": 1, "age": 24, "male": false, "name": "Martha"}
{"id": 2, "age": 49, "male": true, "name": "Jim"}
\.
ALTER TABLE ONLY "JSONtest"
    ADD CONSTRAINT "JSONtest_data_key" UNIQUE (data);

From this, I try to get the data and the type of certain columns:

SELECT "data"#>'{"male"}' FROM "JSONtest"; -- this returns true|false
SELECT jsonb_typeof("data"#>'{"male"}') FROM "JSONtest"; -- this returns 'boolean'

As read in the documentation, currently PostgreSQL can return data as json/jsonb types, if you use a single angle bracket in your query, or as text if you use double angle brackets:

SELECT '{"a":1}'::jsonb->'a' -- this will be of type jsonb
SELECT '{"a":1}'::jsonb->>'a' -- this will be of type string

But I need the actual type of the data in the JSON. What I tried was using the CAST function:

SELECT CAST('{"id":19}'::jsonb->>'a' AS integer) -- this will give back an int type
SELECT CAST('{"id":19}'::jsonb->>'a' AS json_typeof('{"id":19}'::jsonb->>'a')) -- this will obviously give an ERROR, because the type is given as a string

So my question is:

Can you cast with the target type specified as a string?

I could bypass this with a stored function, because there is only 6 options, what a json_typeof could return(object, array, boolean, string, number and null), but if there is a better way, then I would happily go for that.

Thank you all for the answers in advance!

--- edit #1 ---

Here is, what I came up with today as an experiment, but it caused an error with the following text: CASE types integer and text cannot be matched

SELECT
    CASE jsonb_typeof("data"#>"query")
        WHEN 'number' THEN ("data"#>>"query")::int
        WHEN 'string' THEN "data"#>>"query"
        WHEN 'boolean' THEN ("data"#>>"query")::boolean
    END
FROM (
    SELECT
        '{"name" : "Jim", "dogs" : ["Barks", "Fluffy", "Spocky"], "id" : 4}'::jsonb AS "data",
        '{"id"}'::text[] AS "query"
) AS "input"

Functions would cause the same trouble, because I need to specify the return type of it, which in this case cannot be determined.

json_populate_record() and json_to_record() also need type specified.

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.