pgsql text类型字段作为查询条件

遇到了类似的以 json格式作为查询条件的pgsql查询问题，看了你的帖子，试了好几种都报错了，所以想请教下。

表中字段类型是text类型
存储的数据：{"A":{"a":"1","b":"2"}}
请问怎么查 a=1的

这边找不到你要找的人，可以把你的问题发出来看看。
看看是不是这种：

select '{"A":{"a":"1","b":"2"}}'::json->'A'->>'a' as a

img

常用的函数转换方式，希望对你有帮助

OperatorRight Operand TypeDescriptionExample
->intGet JSON array element'[1,2,3]'::json->2
->textGet JSON object field'{"a":1,"b":2}'::json->'b'
->>intGet JSON array element as text'[1,2,3]'::json->>2
->>textGet JSON object field as text'{"a":1,"b":2}'::json->>'b'
#>array of textGet JSON object at specified path'{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}'
#>>array of textGet JSON object at specified path as text'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'
FunctionReturn TypeDescriptionExampleExample Result
array_to_json(anyarray [, pretty_bool])jsonReturns the array as JSON. A PostgreSQL multidimensional array becomes a JSON array of arrays. Line feeds will be added between dimension 1 elements if pretty_bool is true.array_to_json('{{1,5},{99,100}}'::int[])[[1,5],[99,100]]
row_to_json(record [, pretty_bool])jsonReturns the row as JSON. Line feeds will be added between level 1 elements if pretty_bool is true.row_to_json(row(1,'foo')){"f1":1,"f2":"foo"}
to_json(anyelement)jsonReturns the value as JSON. If the data type is not built in, and there is a cast from the type to json, the cast function will be used to perform the conversion. Otherwise, for any value other than a number, a Boolean, or a null value, the text representation will be used, escaped and quoted so that it is legal JSON.to_json('Fred said "Hi."'::text)"Fred said \"Hi.\""
json_array_length(json)intReturns the number of elements in the outermost JSON array.json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')5
json_each(json)SETOF key text, value jsonExpands the outermost JSON object into a set of key/value pairs.select * from json_each('{"a":"foo", "b":"bar"}')` key
json_each_text(from_json json)SETOF key text, value textExpands the outermost JSON object into a set of key/value pairs. The returned value will be of type text.select * from json_each_text('{"a":"foo", "b":"bar"}')` key
json_extract_path(from_json json, VARIADIC path_elems text[])jsonReturns JSON object pointed to by path_elems.json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4'){"f5":99,"f6":"foo"}
json_extract_path_text(from_json json, VARIADIC path_elems text[])textReturns JSON object pointed to by path_elems.json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6')foo
json_object_keys(json)SETOF textReturns set of keys in the JSON object. Only the "outer" object will be displayed.json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')json_object_keys ------------------ f1 f2
json_populate_record(base anyelement, from_json json, [, use_json_as_text bool=false]anyelementExpands the object in from_json to a row whose columns match the record type defined by base. Conversion will be best effort; columns in base with no corresponding key in from_json will be left null. If a column is specified more than once, the last value is used.select * from json_populate_record(null::x, '{"a":1,"b":2}')` a
json_populate_recordset(base anyelement, from_json json, [, use_json_as_text bool=false]SETOF anyelementExpands the outermost set of objects in from_json to a set whose columns match the record type defined by base. Conversion will be best effort; columns in base with no corresponding key in from_json will be left null. If a column is specified more than once, the last value is used.select * from json_populate_recordset(null::x, '[{"a":1,"b":2},{"a":3,"b":4}]')` a
json_array_elements(json)SETOF jsonExpands a JSON array to a set of JSON elements.json_array_elements('[1,true, [2,false]]')value ----------- 1 true [2,false]

%"a":"1"% 可以吗？