How to get only those rows of a Postgresql stored json, that match a schema

Question

Assume a Postgresql table which contains a JSON column. Now how do I get only those table rows which match a certain 'JSON-Schema'?

I was hoping that

select category#>'{Kategorie}' from dataset where category#>'{Kategorie}' is not null

would only get me those rows which match the {Kategorie} Schema.

Have a look at http://stackoverflow.com/questions/19422640/how-to-query-for-null-values-in-json-field-type-postgresql. — Dmitri Goldring, Jan 17 '14 at 22:02

score 0 · Answer 1 · answered Jan 18 '14 at 07:08

0

Thanks to the pointer provided by @dommage, the syntax actually is

select category#>'{Kategorie}' from dataset where (category#>'{Kategorie}') is not null

The parantheses arround the json-extraction operation in the where clause are crucial.

answered Jan 18 '14 at 07:08

JohnDoe

1 Answers1