当前位置：首页 » 编程语言 » sql解析双层json
扩展阅读
提分平台的账号密码是什么 2023-08-31 22:07:10
webinf下怎么引入js 2023-08-31 21:54:13
堡垒机怎么打开web 2023-08-31 21:54:11

sql解析双层json

发布时间: 2022-06-17 23:29:19

1. spark sql多层json怎么查

spark sql多层json怎么查
Spark SQL是支持在Spark中使用Sql、HiveSql、Scala中的关系型查询表达式。它的...jsonRdd - 从一个已存在的RDD中加载数据,其中每一个RDD元素都是一个

2. sql中json解析

你好！

withtas(select'a:[{f:,h:,checindate:''month1：,year:,day:'',checkoutdate:''month：,year:,day:'',},
{checindate:''month2：,year:,day:,'',checkoutdate:''month：,year:,day},
{checindate:''month3：,year:,day:,'',checkoutdate:''month：,year:,day}]'strfromal)
,t1as(SELECTsubstr(str,instr(str,'[')+1,instr(str,']')-instr(str,'[')-1)strFROMT)
,t2as(selectsubstr(str,instr(str,'{')+1,instr(str,'}')-instr(str,'{')-1)strfromt1)
selectstr,substr(str,instr(str,'checindate')+12,instr(str,'checkoutdate')-instr(str,'checindate')-12)fromt2;

得到第一个checindate，直接截取字符串就可以了

别搞得那么复杂了

3. sql 处理 json

json的数据json.loads进来以后会变成一个json的对象，你需要自己把python对象中的字段值取出来，拼成sql语句你可以把这个过程封装成一个函数importjsondefsave_json(json_str):obj=json.loads(json_str)sql='insertintotblvalues("%s")'%obj['id']#这里注意编码，要转成数据库的编码格式#blabla

4. 请教一个SparkSQL解析内嵌有json的csv的问题

一个SparkSQL解析内嵌有json的csv的问题
用excel打开，粘贴出来数据是这样：
uid,uuid,event_type,event_data,created_at
123456,abcdefabcdefabcdef,some-action,"{""ration"": ""20"", ""questionId"": ""123456""}",1476201605

这里的event_data是一个json，使用下面的代码读取：
val usereventDF = spark.read.format("csv").option("header", "true").option("inferSchema", "true")
.load("/Users/xxx/Desktop/event.csv")
.createOrReplaceTempView("t_event")

当select *的时候，会发现created_at字段的输出被按照前面json当中的逗号识别了：
+-------+--------------------+--------------+--------------------+--------------------+
| uid| uuid| event_type| event_data| created_at|
+-------+--------------------+--------------+--------------------+--------------------+
|123456|abcdefabcdefabcdef...|some-action|"{""ration"": "...| ""questionId"": ...|

即便使用get_json_object，也会发现直接输出'$'就是个 "{"

5. SQL server存储过程实现JSON数据解析，然后插入数据库表求高手指点

两种方式
1、SQL有个charindex 函数，可以用这个函数配合substr实现 split功能实现循环插入
2、sql 2008以上存储过程支持表值参数，json反序列化在程序里更方便，所以反序列化之后通过表值参数传递

6. java将json数据解析为sql语句

importjava.util.Iterator;
importjava.util.Set;
importjava.util.Map.Entry;

importcom.google.gson.JsonArray;
importcom.google.gson.JsonElement;
importcom.google.gson.JsonObject;
importcom.google.gson.JsonParser;

publicclassSql
{
	publicstaticStringparseSQL(Stringjson)
	{
		JsonParserparser=newJsonParser();
		JsonObjectobj=(JsonObject)parser.parse(json);;
		Stringtable=obj.get("table").getAsString();
		Stringop_type=obj.get("op_type").getAsString();
		Stringsql="";
		if("I".equals(op_type))
		{
			sql+="INSERTINTO"+table+"(";
			JsonObjectafter=(JsonObject)obj.get("after");
			Set<Entry<String,JsonElement>>entry=after.entrySet();
			Iterator<Entry<String,JsonElement>>it=entry.iterator();
			Stringvs="values(";
			while(it.hasNext())
			{
				Entry<String,JsonElement>elem=it.next();
				Stringkey=elem.getKey();
				Stringval=elem.getValue().toString();
				sql+=key+",";
				vs+=val+",";
			}
			sql=sql.replaceAll(",\s*$","");
			vs=vs.replaceAll(",\s*$","");
			sql+=")"+vs+")";
		}
		elseif("U".equals(op_type))
		{
			sql+="UPDATE"+table+"SET";
			JsonObjectafter=(JsonObject)obj.get("after");
			Set<Entry<String,JsonElement>>entry=after.entrySet();
			Iterator<Entry<String,JsonElement>>it=entry.iterator();
			while(it.hasNext())
			{
				Entry<String,JsonElement>elem=it.next();
				Stringkey=elem.getKey();
				Stringval=elem.getValue().toString();
				sql+=key+"="+val+",";
			}
			sql=sql.replaceAll(",\s*$","");
			sql+="WHERE";
			after=(JsonObject)obj.get("before");
			entry=after.entrySet();
			it=entry.iterator();
			while(it.hasNext())
			{
				Entry<String,JsonElement>elem=it.next();
				Stringkey=elem.getKey();
				Stringval=elem.getValue().toString();
				sql+=key+"="+val+"AND";
			}
			sql=sql.replaceAll("\s*AND\s*$","");
		}
		elseif("D".equals(op_type))
		{
			sql+="DELETEFROM"+table+"WHERE";
			JsonObjectafter=(JsonObject)obj.get("before");
			Set<Entry<String,JsonElement>>entry=after.entrySet();
			Iterator<Entry<String,JsonElement>>it=entry.iterator();
			while(it.hasNext())
			{
				Entry<String,JsonElement>elem=it.next();
				Stringkey=elem.getKey();
				Stringval=elem.getValue().toString();
				sql+=key+"="+val+"AND";
			}
			sql=sql.replaceAll("\s*AND\s*$","");
		}
		returnsql;
	}

	publicstaticvoidmain(String[]args)
	{
		Stringinsert=
		"{"table":"GG.TCUSTORD","op_type":"I","op_ts":"2013-06-0222:14:36.000000","current_ts":"2015-09-18T13:39:35.447000","pos":"00000000000000001444","tokens":{"R":"AADPkvAAEAAEqL2AAA"},"after":{"CUST_CODE":"WILL","ORDER_DATE":"1994-09-30:15:33:00","PRODUCT_CODE":"CAR","ORDER_ID":"144","PRODUCT_PRICE":17520.00,"PRODUCT_AMOUNT":3,"TRANSACTION_ID":"100"}}";
		Stringupdate=
		"{"table":"GG.TCUSTORD","op_type":"U","op_ts":"2013-06-0222:14:41.000000","current_ts":"2015-09-18T13:39:35.748000","pos":"00000000000000002891","tokens":{"L":"206080450","6":"9.0.80330","R":"AADPkvAAEAAEqLzAAC"},"before":{"CUST_CODE":"BILL","ORDER_DATE":"1995-12-31:15:00:00","PRODUCT_CODE":"CAR","ORDER_ID":"765","PRODUCT_PRICE":15000.00,"PRODUCT_AMOUNT":3,"TRANSACTION_ID":"100"},"after":{"CUST_CODE":"BILL","ORDER_DATE":"1995-12-31:15:00:00","PRODUCT_CODE":"CAR","ORDER_ID":"765","PRODUCT_PRICE":14000.00,"PRODUCT_AMOUNT":3,"TRANSCATION_ID":"100"}}";
		Stringdelete=
		"{"table":"GG.TCUSTORD","op_type":"D","op_ts":"2013-06-0222:14:41.000000","current_ts":"2015-09-18T13:39:35.766000","pos":"00000000000000004338","tokens":{"L":"206080450","6":"9.0.80330","R":"AADPkvAAEAAEqLzAAC"},"before":{"CUST_CODE":"DAVE","ORDER_DATE":"1993-11-03:07:51:35","PRODUCT_CODE":"PLANE","ORDER_ID":"600"}}";
		System.out.println(parseSQL(insert));
		System.out.println(parseSQL(update));
		System.out.println(parseSQL(delete));
	}
}

7. db2 sql怎么解析json

jsp页面的数据转换成json格式可以采用js来解析：
例如在ation中：
bookList = new ArrayList<Books>();
JSONObject json = new JSONObject();
json.accumulate("bookList", bookList);
json.accumulate("pageNum", pageNum);
json.accumulate("totalPages", totalPages);
json.accumulate("totalNum", totalNum);
jsonObj = json.toString();

8. 求救，mysql怎么解析json

DELIMITER $$ USE `dw`$$ DROP FUNCTION IF EXISTS `fn_Json_getKeyValue`$$ CREATE DEFINER=`data`@`%` FUNCTION `fn_Json_getKeyValue`( in_JsonArray VARCHAR(4096),#JSON数组字符串 in_Index TINYINT, #JSON对象序号，序号从1开始 in_KeyName VARCHAR(64)#键名 ) RETURNS VARCHAR(512) CHARSET utf8 BEGIN DECLARE vs_return VARCHAR(4096); DECLARE vs_JsonArray, vs_Json, vs_KeyName VARCHAR(4096); #declare vs_Json varchar(4096); DECLARE vi_pos1, vi_pos2 SMALLINT UNSIGNED; #写监控日志 #insert into dw.t_etl_log(sp_name, title, description) #values('dw.fn_Json_getKeyValue', '通过Json键名取键值', concat('in_JsonArray=', in_JsonArray)); SET vs_JsonArray = TRIM(in_JsonArray); SET vs_KeyName = TRIM(in_KeyName); IF vs_JsonArray = '' OR vs_JsonArray IS NULL OR vs_KeyName = '' OR vs_KeyName IS NULL OR in_Index 0 THEN #如果键名存在 SET vi_pos1 = vi_pos1 + CHAR_LENGTH(vs_KeyName); SET vi_pos2 = LOCATE(',', vs_json, vi_pos1); IF vi_pos2 = 0 THEN #最后一个元素没有','分隔符，也没有结束符'}' SET vi_pos2 = CHAR_LENGTH(vs_json) + 1; END IF; SET vs_return = REPLACE(MID(vs_json, vi_pos1, vi_pos2 - vi_pos1), '"', ''); END IF; END IF; END IF; RETURN(vs_return); END$$ DELIMITER ; 测试： {"old_current_score":"2","new_current_score":"0","old_grade_id":"1","new_grade_id":"1","grade_time":"2016-04-09 00:43:26","grade_upgrade_time":"2017-04-09 00:43:26"} select fn_Json_getKeyValue(reason,1,'old_grade_id')

9. 如何将网上json数据库解析到本地的sql数据库中啊

取得数据最开始是json字符串->转化为json数据->保存到数据库

json数据知道的话，很简单了

10. sql中对json数据字段的查询

先取出string，再在内存里转换为对象并检查。
ps：存json是没问题，但又想存json又想直接查，违反了数据库的范式。

阅读全文

与sql解析双层json相关的内容

本站内容整理源于互联网，如有问题请联系解决。
Copyright design: www.gotrillian.com since 2022