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.)

Thanks. What's "latest" now doesn't mean "latest" a year later when someone else reads the question & answers, after all. — Craig Ringer
– Craig Ringer, Commented Jun 16, 2014 at 3:17

satsie · Accepted Answer · 2018-03-20 15:23:49Z

11

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;

answered Mar 20, 2018 at 15:23

satsie

3993 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Craig Ringer · Accepted Answer · 2014-06-16 03:19:37Z

5

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

answered Jun 16, 2014 at 3:19

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

5 Comments

alxlvt Over a year ago

I still get an error, though it's different: ERROR: column table1.json_data does not exist...

Craig Ringer Over a year ago

Well, there's no possible way I can help you with that, as you haven't shown your table definitions. The query works fine, as you can verify by putting a literal json value in place of table1.json_data.

willglynn Over a year ago

Your question says "Let's assume the table is called table1 and the particular column, json_data, has something like {…json…}". If table1.json_data does not exist, that's on you; this answer is correct.

nikhil84 Over a year ago

how to put where clause if we want to retrieve row based on the matching of data from json_data like for case based on 'item 1'

Craig Ringer Over a year ago

@nikhil84 Please post a new question. Put a link in the new question back to this one for context if you want.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related