如何过滤内部查询键之类的Postgresql jsonb

id        data
0a        {"name" : "x1", "source_extended_attributes": {"alexa_pageviews": 6000}}
7ee       {"name" : "x2", "source_extended_attributes": {"alexa_pageviews": 6000}}
8d        {"name" : "x3", "source_extended_attributes": {"alexa_pageviews": 6000}}

The data column is defined as jsonb.

I need to use a select where alexa_pageviews but I can't.

I can use name like where data->>'name' = 'x1'

我需要如何在PostgreSQL查询中的键内部访问键

评论
  • 洋子舒
    洋子舒 回复

    Simple: data->'source_extended_attributes'->>'alexa_pageviews' If you want to compare the value with something, there are several ways.

    作为文字:

    data->'source_extended_attributes'->>'alexa_pageviews' like '6%'
    

    作为数字:

    (data->'source_extended_attributes'->>'alexa_pageviews')::int > 5000
    

    作为JSON数字:

    data->'source_extended_attributes'->'alexa_pageviews' > '999'