ERROR: function regexp_matches(jsonb, unknown) does not exist in Tableau but works elsewhere

Question

I have a column called "Bakery Activity" whose values are all JSONs that look like this:

{"flavors": [
    {"d4js95-1cc5-4asn-asb48-1a781aa83": "chocolate"},
    {"dc45n-jnsa9i-83ysg-81d4d7fae": "peanutButter"}], 
 "degreesToCook": 375, 
 "ingredients": {
    "d4js95-1cc5-4asn-asb48-1a781aa83": [
        "1nemw49-b9s88e-4750-bty0-bei8smr1eb", 
        "98h9nd8-3mo3-baef-2fe682n48d29"]
    },
 "numOfPiesBaked": 1,
 "numberOfSlicesCreated": 6
}

I'm trying to extract the number of pies baked with a regex function in Tableau. Specifically, this one:

REGEXP_EXTRACT([Bakery Activity], '"numOfPiesBaked":"?([^\n,}]*)')

However, when I try to throw this calculated field into my text table, I get an error saying:

ERROR: function regexp_matches(jsonb, unknown) does not exist;

Error while executing the query

Worth noting is that my data source is PostgreSQL, which Tableau regex functions support; not all of my entries have numOfPiesBaked in them; when I run this in a simulator I get the correct extraction (actually, I get "numOfPiesBaked": 1" but removing the field name is a problem for another time).

What might be causing this error?

Are you using REGEXP_EXTRACT... or REGEXP_MATCHES ? Please post your entire query. — GMB
@GMB I'm using REGEXP_EXTRACT, but the error text refers to regexp_matches. The formula I posted is my entire calculated field — yoni

Erwin Brandstetter Erwin Brandstetter · Accepted Answer · 2018-12-17T03:33:21

In short: Wrong data type, wrong function, wrong approach.

REGEXP_EXTRACT is obviously an abstraction layer of your client (Tableau), which is translated to regexp_matches() for Postgres. But that function expects text input. Since there is no assignment cast for jsonb -> text (for good reasons) you have to add an explicit cast to make it work, like:

SELECT regexp_matches("Bakery Activity"::text, '"numOfPiesBaked":"?([^\n,}]*)')

(The second argument can be an untyped string literal, Postgres function type resolution can defer the suitable data type text.)

Modern versions of Postgres also have regexp_match() returning a single row (unlike regexp_matches), which would seem like the better translation.

But regular expressions are the wrong approach to begin with.
Use the simple json/jsonb operator ->>:

SELECT "Bakery Activity"->>'numOfPiesBaked';

Returns '1' in your example.
If you know the value to be a valid integer, you can cast it right away:

SELECT ("Bakery Activity"->>'numOfPiesBaked')::int;

ERROR: function regexp_matches(jsonb, unknown) does not exist in Tableau but works elsewhere

2 Answers