Error for Querying JSON in Postgres: function json_extract_path_text(text, text) does not exist

Question

I'm trying to set up a view based on a query to a text column that contains JSON in the latest version (9.3.4) of Postgres, but I get an error that I have not been able to find any discussion about.

Let's assume the table is called table1 and the particular column, json_data, has something like

{"item1": "value1", "item2": "value2", "item3": 3, "item4": 4, "item5": 5}

Here is my query:

SELECT 
json_extract_path_text((table1.json_data)::text, 
('item1'::character varying)::text) AS item1
FROM
table1

The error I get is

ERROR:  function json_extract_path_text(text, text) does not exist
LINE 2: json_extract_path_text((table1.json_data)...
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

I'm lost on how to fix this. (Also, I have a similar view that works completely fine using the same syntax on a similar text column in the particular table.)

Answer 1

Since table1.json_data is already text you need to cast it to json. You also don't need to specify the table name because it is in the FROM clause.

SELECT json_extract_path_text(json_data::json,'item1') AS item1 FROM table1;

Answer 2

For some reason, you're casting the json input to text:

json_extract_path_text((table1.json_data)::text

don't do that.

SELECT 
    json_extract_path_text(
        table1.json_data,
        'item1'
    ) AS item1
FROM table1