SQLSERVER 2008 解析 Json 格式数据

SQLSERVER 2008 解析 Json 格式数据

Json 格式 ：'[{"SN":001,"Name":张小明,"CardNO":320326199711074812},{"SN":002,"Name":王小二,"CardNO":320500197110925064},{"SN":003,"Name":林双双,"CardNO":310410198508025027},{"SN":004,"Name":杜强,"CardNO":303500200008015012}]'

需要的结果
SN Name CardNO
001 张小明 320326199711074812
002 王小二 320500197110925064
003 林双双 310410198508025027
004 杜强 303500200008015012

参考这个：

将计算机编译码改成uit一8

使用jsoncpp这个开源库，解析你的json串

以下是伪代码，作为参考

char* jsonBuff = [{"SN":001,"Name":张小明,"CardNO":320326199711074812},{"SN":002,"Name":王小二,"CardNO":320500197110925064},{"SN":003,"Name":林双双,"CardNO":310410198508025027},{"SN":004,"Name":杜强,"CardNO":303500200008015012}];

Json::Reader reader(Json::Features::strictMode());
Json::Value terms;

if (!reader.parse(jsonBuff, terms, false))
{
    return false;
}

uint32_t size = terms.size();
for (uint32_t n = 0; n < size; n++)
{
    Json::Value &item = terms[n];
    GetFieldFromJson(item, "SN", sn);
    GetFieldFromJson(item, "Name", name);
    GetFieldFromJson(item, "CardNO", cardno);
}

我的需求是 JSON格式的数据作为一个参数 传递到 存储过程里，然后在存储过程里解析这个 Json数据 然后将解析结果保存到一个数据表里。

object JsonLog {
  def main(args: Array[String]): Unit = {
      val spark: SparkSession = SparkSession.builder().master("local").appName("readJson").getOrCreate()
      val sc: SparkContext = spark.sparkContext
      import spark.implicits._
      import org.apache.spark.sql.functions._

     val readDF: DataFrame = sc.textFile("in/Text.txt").toDF()

      readDF.printSchema()


    val readDF1: DataFrame = readDF.select(
      get_json_object($"value", "$.name").as("name"),
      get_json_object($"value", "$.url").as("url"),
      get_json_object($"value", "$.address").as("address"),
      get_json_object($"value", "$.domain_list").as("domain_list"))


    val textDF: DataFrame = readDF1.select($"domain_list")

    val arrayType = ArrayType(StructType(StructField("name",StringType) ::StructField("url",StringType)::Nil))
    val readDF2: DataFrame = textDF.select(from_json($"domain_list",arrayType).as("domain_list"))
    val readDF3: DataFrame = readDF2.withColumn("domain_list", explode(col("domain_list")))
      .select($"domain_list.name", $"domain_list.url")

    val readDF5: DataFrame = readDF1.select($"name", $"url", $"city", $"country",
      from_json($"domain_list", ArrayType(StructType(StructField("name", StringType)
        :: StructField("url", StringType) :: Nil))).as("domain_list")
    ).withColumn("domain_list", explode(col("domain_list")))
      .select($"name", $"url", $"city", $"country", $"domain_list.name", $"domain_list.url")

  }
}

类似这样的，json一般都是一层层解析的，先用app转换一下json形式