How do I extract data from the nested json using spark DS?

Question

I have a Spark DS with the below structure, I want to flatten each and extract all the columns. I tried get_json_object like below but it isn't working.

root
  |-- data: struct (nullable = true)
  |    |-- Level: long (nullable = true)
  |    |-- activityName: string (nullable = true)
  |    |-- activityRunId: string (nullable = true)
  |    |-- activityType: string (nullable = true)
  |    |-- category: string (nullable = true) 
  |    |-- correlationId: string (nullable = true)
  |    |-- effectiveIntegrationRuntime: string (nullable = true)
  |    |-- end: string (nullable = true)
  |    |-- level: string (nullable = true)
  |    |-- location: string (nullable = true)
  |    |-- operationName: string (nullable = true)
  |    |-- pipelineName: string (nullable = true)
  |    |-- pipelineRunId: string (nullable = true)
  |    |-- properties: struct (nullable = true)
  |    |    |-- Annotations: array (nullable = true)
  |    |    |    |-- element: string (containsNull = true)
  |    |    |-- Error: struct (nullable = true)
  |    |    |    |-- errorCode: string (nullable = true)


val DS = logDS.select(get_json_object($"data", "$.Level").alias("level"))

Requirement is I want to extract as separate columns

what do you mean by extract? do you mean you want them in separate columns? if so just use .* notation — Ramesh Maharjan, Jul 06 '18 at 05:22
yes want them as a separate columns, so that I can store them in any DB. — prady, Jul 06 '18 at 05:24
just do `val DS = logDS.select($"data.*")` and see if it resolves — Ramesh Maharjan, Jul 06 '18 at 05:27
cool it works but I also want to extract the column "errorcode" which is nested one level further — prady, Jul 06 '18 at 05:33
you can repeat the above step on DS again. hope it helps. try it and if still confusion let me know — Ramesh Maharjan, Jul 06 '18 at 05:38
Its working, Please write this as a solution. I will mark it as answer. Great Thanks ! — prady, Jul 06 '18 at 05:58
I am marking it as duplicate instead as there are a lot of them :) thanks @prady — Ramesh Maharjan, Jul 06 '18 at 06:05

How do I extract data from the nested json using spark DS?

0 Answers0