如何用SQL访问JSON字符串中列表内的role和school值？

首先明确：你的profile字段中，contacts是数组类型的JSON结构，必须先定位到数组中的具体元素，再访问其role/school属性。不同数据库的JSON操作语法存在差异，以下是主流数据库的正确实现方式：

MySQL（5.7及以上版本） #

• 若profile是原生JSON类型列：
  • 获取第一个contact的role：profile->>'$.contacts[0].role'
  • 获取第一个contact的school：profile->>'$.contacts[0].school'
    （->>语法会自动去除字符串结果的引号，若需保留引号用->）
• 若profile是字符串类型（如VARCHAR）：
  需要先将字符串转为JSON类型再操作：

  JSON_UNQUOTE(JSON_EXTRACT(CAST(profile AS JSON), '$.contacts[0].role'))
  

PostgreSQL（9.4及以上版本） #

• 若profile是JSONB类型（推荐使用，性能更优）：
  • 获取role：profile -> 'contacts' -> 0 ->> 'role'
  • 获取school：profile -> 'contacts' -> 0 ->> 'school'
    也可以用路径写法：profile #>> '{contacts,0,role}'
• 若profile是JSON类型，语法与JSONB一致，仅性能略低。

SQL Server（2016及以上版本） #

使用JSON_VALUE函数定位属性：

-- 获取role
JSON_VALUE(profile, '$.contacts[0].role')
-- 获取school
JSON_VALUE(profile, '$.contacts[0].school')

额外注意事项 #

你提供的JSON存在语法错误：开头的[{"id":"X","type":"location"}]直接跟后续键值对，不符合JSON对象的结构规范（正确的JSON对象应该是键值对的集合，开头的数组需归属某个键，比如"locations": [{"id":"X","type":"location"}]）。如果存储的JSON格式无效，所有属性访问操作都会失败，建议先验证并修正JSON的有效性。

内容的提问来源于stack exchange，提问作者AlinaAZ