JSONB query does not work in cockroachdbb

sql

(Gomathivinayagam Muthuvinayagam) #1

I have a table which contains just one field a jsonb column, and inside the json i have id, and list of external ids. All I want to do is flatten referenceId, external_id pair, and then want to do a join.

And I am able to run the following query against postgres, but not able to do the same against cockroachdb.

select wiki_show->>‘reference_id’ reference_id, jsonb_array_elements(wiki_show->‘external_ids’) external_ids from wiki_shows;


(Ron Arévalo) #2

Hey @gvinayagam,

Can you let me know what version of CockroachDB your are currently running?

Thanks,

Ron


(Gomathivinayagam Muthuvinayagam) #3

Build Tag: v2.0.7
Build Time: 2018/12/10 15:56:18
Distribution: CCL
Platform: darwin amd64 (x86_64-apple-darwin13)
Go Version: go1.10
C Compiler: 4.2.1 Compatible Clang 3.8.0 (tags/RELEASE_380/final)
Build SHA-1: 7527d65d98e2ffa6d7d4de945d234876bd0b76a9
Build Type: release

Also I tried the following beta version too

Build Tag: v2.0-beta.20180312
Build Time: 2018/03/12 17:47:59
Distribution: CCL
Platform: darwin amd64
Go Version: go1.10
C Compiler: 4.2.1 Compatible Clang 3.8.0 (tags/RELEASE_380/final)
Build SHA-1: 6fc27750aacdcc11731cca7c92a77398103bec09
Build Type: release

So far what I have found is still postgres’s json support is amazing, and cockroachdb has to go really long way.


(Ron Arévalo) #4

Hey @gvinayagam,

Thanks for the feedback! We are doing our best to have parity with Postgres and feedback like this is essential to that forward progress.

Regarding the issue, if you upgrade to Cockroach 2.1 your query would execute as we have made some updates that would allow for this.

We have some documentation on upgrading from Cockroach 2.0.x here.

Thanks,

Ron