查询PostgreSQL中嵌入的JSON数据

p8ekf7hl  于 2023-10-18  发布在  PostgreSQL
关注(0)|答案(1)|浏览(97)

我至少有10年没有使用PostgreSQL了,对它从来都不太熟悉。我现在发现自己不得不处理PostgreSQL数据库中JSONB属性中嵌入的日期+时间字符串-而且没有取得多大成功。我对查询嵌入在任何关系数据库中的JSON一无所知(当然,除了它是错误的-但我不能在这里修复)。
如果相关的话,服务器端是13.10,我用于测试的psql客户端是9.2
我可以检索数据:

  1. SELECT id, ddata->'domain'->'created_at'
  2. FROM schema.mytable
  3. WHERE id=100
  4. id | ?column?
  5. --------+---------------------------------
  6. 100 | "Tue, 13 Mar 2018 16:10:01 GMT"

但是我不知道如何让PostgreSQL将值解析为日期。
我尝试过的一些事情和相关的错误....

  1. SELECT id, TO_DATE(ddata->'domain'->'created_at', '"Dy, DD Mon YYYY HH24:MI:SS GMT"')
  2. FROM myschema.mytable
  3. WHERE id = 100
  4. No function matches the given name and argument types
  5. SELECT id, ddata->'domain'->'created_at'.datetime()
  6. FROM myschema.mytable
  7. WHERE id = 100
  8. syntax error at or near "."
  9. SELECT id, jsonb_path_query(ddata->'domain'->'created_at', '$[*].datetime("HH24:MI")')
  10. FROM myschema.mytable
  11. WHERE id = 100
  12. ERROR: invalid value "Tu" for "HH24"
  13. DETAIL: Value must be an integer.
  14. SELECT id, jsonb_path_query(ddata->'domain'->'created_at', '$[*].datetime("Dy, DD Mon YYYY HH24:MI:SS GMT")')
  15. FROM myschema.mytable
  16. WHERE id = 100
  17. ERROR: trailing characters remain in input string after datetime format

(the如果我能找到一种将类型转换为字符串的方法,那么上面的块中的第一次尝试看起来很有希望。我找不到这样做的方法)
最后,我想选择日期小于N天的记录-但只有在PostgreSQL解析日期时才能做到这一点。
属性中原始数据的简化版本为:

  1. {
  2. "domain": {
  3. "created_at": "Tue, 13 Mar 2018 16:10:01 GMT"
  4. }
  5. }

更新
自从发布后,我发现了->>运算符。然而,这并没有多大帮助; TO_TIMESTAMP(ddata->'domain'->>'created_at', '"Dy, DD Mon YYYY HH24:MI:SS"')返回所有值的0001-01-01 00:00:00+00 BC

sigwle7e

sigwle7e1#

  1. select ('{
  2. "domain": {
  3. "created_at": "Tue, 13 Mar 2018 16:10:01 GMT"
  4. }
  5. }'::json
  6. ->'domain' ->> 'created_at')::date;
  7. date
  8. ------------
  9. 03/13/2018

对于timestamp

  1. select ('{
  2. "domain": {
  3. "created_at": "Tue, 13 Mar 2018 16:10:01 GMT"
  4. }
  5. }'::json
  6. ->'domain' ->> 'created_at')::timestamptz;
  7. timestamptz
  8. -------------------------
  9. 03/13/2018 09:10:01 PDT

首先使用->'domain'对象的内容提取为JSON,然后使用->>'created_at'对象的内容提取为text。然后使用::date强制转换转换为datetimestamp(tz)值。

展开查看全部

相关问题