PostgreSQL无法在标量上调用json_object_keys [英] PostgreSQL cannot call json_object_keys on a scalar

问题描述

我有一个带有JSON列的PostgreSQL表，并且我试图获取该列中所有不同键的列表.我创建了一个查询来做到这一点:

I have a PostgreSQL table with a JSON column, and I'm trying to get a list of all the distinct keys in that column. I created a query to do this:

SELECT DISTINCT json_object_keys(j) FROM t;

其中t是表，而j是JSON列.这可以正确处理少量数据，它将列出j中存在的所有键，而无需重复它们.但是，添加更多数据后，该数据将不再起作用，并显示错误消息:

Where t is the table and j is the JSON column. This worked on a small set of data correctly, it would list all the keys that exist in j, without repeating them. However, after adding a lot more data, it doesn't work anymore, giving the error:

ERROR:  cannot call json_object_keys on a scalar

我不确定为什么会这样.通过一次只将查询限制到某些行，我发现了导致错误的行.在该行中，j是null.但是，调用SELECT json_object_keys(null);不会导致此错误，而调用SELECT json_object_keys(j) FROM t WHERE id=12;会导致此错误，并且此行中的j只是null.我不太确定该从哪里去.

I'm not sure exactly why this is happening. By just limiting the query to certain rows one at a time, I found one that causes the error. In that row, j is null. However, calling SELECT json_object_keys(null); does not cause this error, while calling SELECT json_object_keys(j) FROM t WHERE id=12; does, and j in this row is just null. I'm not really sure where to go from here.

所以我想我的问题是可能是什么原因造成的，如何解决或阻止它发生?

So I guess my question is what could be causing this, and how can I either work around it or prevent it from happening?

运行PostgreSQL 9.3.9

Running PostgreSQL 9.3.9

好的，我可能已经抢先发表了此内容.我发现有问题的行中的j不是null，它是'null'::json，仅选择该行并不清楚.这确实会导致标量错误，所以现在我只需要找出一种选择j不是'null'::json的行的方法.

Ok, I may have posted this a little preemptively. I figured out that j in the problematic row isn't null, it's 'null'::json, which wasn't clear from just selecting the row. This does cause the scalar error, so now I just have to figure out a way to select the rows where j isn't 'null'::json.

推荐答案

我尝试了此查询，以通过该查询过滤出'null'::json值:

I tried this query, to filter out the 'null'::json values with this query:

SELECT DISTINCT json_object_keys(j) from t WHERE j <> 'null'::json;

但是，显然没有json<> json运算符，所以我不得不将其转换为文本并进行比较.

However, apparently there is no json <> json operator, so I had to cast it to text and compare.

SELECT DISTINCT json_object_keys(j) from t WHERE j::TEXT <> 'null';

这有效！我不是Postgres专家，所以这可能不是执行此检查的最有效方法.

This works! I'm not a Postgres expert though, so this may not be the most effecient way of doing this check.

这篇关于PostgreSQL无法在标量上调用json_object_keys的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！