时间戳

首页 > 解决方案 > SQL Server 查询仅从有效 JSON 中返回第一行

json - SQL Server 查询仅从有效 JSON 中返回第一行

问题描述

为什么下面的这个查询只返回 JSON 的第一行？

DECLARE @JSON NVARCHAR(MAX)

SELECT @JSON = BulkColumn
FROM OPENROWSET 
(BULK 'C:/Users/jose.carvalho/Downloads/output/output/dataset_000.txt', SINGLE_CLOB) 
AS j

SELECT *
FROM OPENJSON (@JSON) 
WITH (  timestamp VARCHAR(20) '$.timestamp', 
        type VARCHAR(20) '$.type',
        role VARCHAR(20) '$.role',
        actor_id varchar(100) '$.actor.id',
        actor_name varchar(100) '$.actor.first_name',
        actor_name varchar(100) '$.actor.last_name',
        last_name varchar(100) '$.actor.gender',
        gender_name varchar(100) '$.movie.id',
        movie_id varchar(100) '$.movie.name',
        movie_name varchar(100) '$.movie.name',
        movie_year varchar(100) '$.movie.year',
        movie_rank varchar(100) '$.movie.rank') -- new column name

文件内的 JSON 数据：

{"timestamp": 1293847260, "type": "role", "role": "Julia 'Julie' Pendleton", "actor": {"id": 560320, "first_name": "Betty", "last_name": "Bouton", "gender": "F"}, "movie": {"id": 76619, "name": "Daddy-Long-Legs", "year": 1919, "rank": 7.1}}
{"timestamp": 1293847320, "type": "role", "role": "Noirtier", "actor": {"id": 345936, "first_name": "Carlo", "last_name": "Ninchi", "gender": "M"}, "movie": {"id": 384908, "name": "\"Conte di Montecristo, Il\"", "year": 1966}}
{"timestamp": 1293847380, "type": "role", "actor": {"id": 484191, "first_name": "Yasuharu", "last_name": "Umeono", "gender": "M"}, "movie": {"id": 127329, "name": "Ghulam", "year": 2005, "rank": 7.1}}
{"timestamp": 1293847440, "type": "actor", "id": 116238, "first_name": "Alayna", "last_name": "Dekelbaum", "gender": "M"}
{"timestamp": 1293847500, "type": "movie", "id": 16132, "name": "And There Were Seven", "year": 1991}
{"timestamp": 1293847560, "type": "movie", "id": 338669, "name": "Tre fratelli calciatori, I", "year": 2005}
{"timestamp": 1293847620, "type": "role", "role": "Joseph Mendel", "actor": {"id": 300510, "first_name": "Garry", "last_name": "Marsh", "gender": "M"}, "movie": {"id": 194772, "name": "Lost Chord, The", "year": 1934}}
{"timestamp": 1293847680, "type": "movie", "id": 143796, "name": "Hermie & Friends", "year": 2005}
{"timestamp": 1293847740, "type": "actor", "id": 525725, "first_name": "Yu (II)", "last_name": "Zhang", "gender": "M"}
{"timestamp": 1293847800, "type": "actor", "id": 174338, "first_name": "Fran\u00e7ois (I)", "last_name": "Girard", "gender": "M"}

标签： jsonsql-server

解决方案

您的 Json 格式不正确，因为它没有comma(,)在每个 Json 数据的末尾。

小提琴演示

尝试这个

DECLARE @JSON NVARCHAR(MAX)

SELECT 
@JSON = '[' + RIGHT(REPLACE(d.BulkColumn, '{"timestamp":', ',{"timestamp":'),LEN(REPLACE(d.BulkColumn, '{"timestamp":', ',{"timestamp":'))-1)+ ']'
FROM OPENROWSET 
(BULK 'D:/dataset_000.txt', SINGLE_CLOB) d


SELECT @JSON
SELECT *
FROM OPENJSON (@JSON) 
WITH (  timestamp VARCHAR(20) '$.timestamp', 
        type VARCHAR(20) '$.type',
        role VARCHAR(20) '$.role',
        actor_id varchar(100) '$.actor.id',
        actor_name varchar(100) '$.actor.first_name',
        actor_name varchar(100) '$.actor.last_name',
        last_name varchar(100) '$.actor.gender',
        gender_name varchar(100) '$.movie.id',
        movie_id varchar(100) '$.movie.name',
        movie_name varchar(100) '$.movie.name',
        movie_year varchar(100) '$.movie.year',
        movie_rank varchar(100) '$.movie.rank') -- new column name

推荐阅读