title: MariaDB parse a JSON String
body: <p>This works in SQL Server:</p>
  <pre><code>    declare @jstring varchar(max)='{
      &quot;FieldInfo&quot;: {
          &quot;Template&quot;: &quot;Babies&quot;,
          &quot;Groups&quot;: [{
              &quot;Group&quot;: &quot;Required&quot;,
              &quot;SortOrder&quot;: 1,
              &quot;BackgroundColor&quot;: &quot;FCD5B4&quot;,
              &quot;Fields&quot;: [{
                  &quot;fieldName&quot;: &quot;feed_product_type&quot;,
                  &quot;SortOrder&quot;: 1,
                  &quot;labelName&quot;: &quot;Product Type&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 0
              }, {
                  &quot;fieldName&quot;: &quot;item_sku&quot;,
                  &quot;SortOrder&quot;: 2,
                  &quot;labelName&quot;: &quot;Seller SKU&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 1
              }, {
                  &quot;fieldName&quot;: &quot;brand_name&quot;,
                  &quot;SortOrder&quot;: 3,
                  &quot;labelName&quot;: &quot;Brand&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 2
              }, {
                  &quot;fieldName&quot;: &quot;item_name&quot;,
                  &quot;SortOrder&quot;: 4,
                  &quot;labelName&quot;: &quot;Title&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 3
              }, {
                  &quot;fieldName&quot;: &quot;external_product_id&quot;,
                  &quot;SortOrder&quot;: 5,
                  &quot;labelName&quot;: &quot;Product ID&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 4
              }, {
                  &quot;fieldName&quot;: &quot;external_product_id_type&quot;,
                  &quot;SortOrder&quot;: 6,
                  &quot;labelName&quot;: &quot;Product ID Type&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 5
              }, {
                  &quot;fieldName&quot;: &quot;item_type&quot;,
                  &quot;SortOrder&quot;: 7,
                  &quot;labelName&quot;: &quot;Item Type Keyword&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 6
              }, {
                  &quot;fieldName&quot;: &quot;model&quot;,
                  &quot;SortOrder&quot;: 8,
                  &quot;labelName&quot;: &quot;Model Number&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 7
              }, {
                  &quot;fieldName&quot;: &quot;manufacturer&quot;,
                  &quot;SortOrder&quot;: 9,
                  &quot;labelName&quot;: &quot;Manufacturer&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 8
              }, {
                  &quot;fieldName&quot;: &quot;part_number&quot;,
                  &quot;SortOrder&quot;: 10,
                  &quot;labelName&quot;: &quot;Part Number&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 9
              }, {
                  &quot;fieldName&quot;: &quot;mfg_minimum&quot;,
                  &quot;SortOrder&quot;: 11,
                  &quot;labelName&quot;: &quot;Minimum Manufacturer Age  Recommended&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 10
              }, {
                  &quot;fieldName&quot;: &quot;standard_price&quot;,
                  &quot;SortOrder&quot;: 12,
                  &quot;labelName&quot;: &quot;Standard Price&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 11
              }, {
                  &quot;fieldName&quot;: &quot;quantity&quot;,
                  &quot;SortOrder&quot;: 13,
                  &quot;labelName&quot;: &quot;Quantity&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 12
              }, {
                  &quot;fieldName&quot;: &quot;main_image_url&quot;,
                  &quot;SortOrder&quot;: 14,
                  &quot;labelName&quot;: &quot;Main Image URL&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 13
              }]
          }, {
              &quot;Group&quot;: &quot;Images&quot;,
              &quot;SortOrder&quot;: 2,
              &quot;BackgroundColor&quot;: &quot;FFFF00&quot;,
              &quot;Fields&quot;: [{
                  &quot;fieldName&quot;: &quot;other_image_url1&quot;,
                  &quot;SortOrder&quot;: 15,
                  &quot;labelName&quot;: &quot;Other Image URL1&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 14
              }, {
                  &quot;fieldName&quot;: &quot;other_image_url2&quot;,
                  &quot;SortOrder&quot;: 16,
                  &quot;labelName&quot;: &quot;Other Image URL2&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 15
              }, {
                  &quot;fieldName&quot;: &quot;other_image_url3&quot;,
                  &quot;SortOrder&quot;: 17,
                  &quot;labelName&quot;: &quot;Other Image URL3&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 16
              }, {
                  &quot;fieldName&quot;: &quot;other_image_url4&quot;,
                  &quot;SortOrder&quot;: 18,
                  &quot;labelName&quot;: &quot;Other Image URL4&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 17
              }, {
                  &quot;fieldName&quot;: &quot;other_image_url5&quot;,
                  &quot;SortOrder&quot;: 19,
                  &quot;labelName&quot;: &quot;Other Image URL5&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 18
              }, {
                  &quot;fieldName&quot;: &quot;other_image_url6&quot;,
                  &quot;SortOrder&quot;: 20,
                  &quot;labelName&quot;: &quot;Other Image URL6&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 19
              }, {
                  &quot;fieldName&quot;: &quot;other_image_url7&quot;,
                  &quot;SortOrder&quot;: 21,
                  &quot;labelName&quot;: &quot;Other Image URL7&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 20
              }, {
                  &quot;fieldName&quot;: &quot;other_image_url8&quot;,
                  &quot;SortOrder&quot;: 22,
                  &quot;labelName&quot;: &quot;Other Image URL8&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 21
              }, {
                  &quot;fieldName&quot;: &quot;swatch_image_url&quot;,
                  &quot;SortOrder&quot;: 23,
                  &quot;labelName&quot;: &quot;Swatch Image URL&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 22
              }]
          }, {
              &quot;Group&quot;: &quot;Variation&quot;,
              &quot;SortOrder&quot;: 3,
              &quot;BackgroundColor&quot;: &quot;FF8080&quot;,
              &quot;Fields&quot;: [{
                  &quot;fieldName&quot;: &quot;parent_child&quot;,
                  &quot;SortOrder&quot;: 24,
                  &quot;labelName&quot;: &quot;Parentage&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 23
              }, {
                  &quot;fieldName&quot;: &quot;relationship_type&quot;,
                  &quot;SortOrder&quot;: 25,
                  &quot;labelName&quot;: &quot;Relationship Type&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 24
              }, {
                  &quot;fieldName&quot;: &quot;parent_sku&quot;,
                  &quot;SortOrder&quot;: 26,
                  &quot;labelName&quot;: &quot;Parent SKU&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 25
              }, {
                  &quot;fieldName&quot;: &quot;variation_theme&quot;,
                  &quot;SortOrder&quot;: 27,
                  &quot;labelName&quot;: &quot;Variation Theme&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 26
              }]
          }, {
              &quot;Group&quot;: &quot;Basic&quot;,
              &quot;SortOrder&quot;: 4,
              &quot;BackgroundColor&quot;: &quot;F8A45E&quot;,
              &quot;Fields&quot;: [{
                  &quot;fieldName&quot;: &quot;update_delete&quot;,
                  &quot;SortOrder&quot;: 28,
                  &quot;labelName&quot;: &quot;Update Delete&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 27
              }, {
                  &quot;fieldName&quot;: &quot;product_description&quot;,
                  &quot;SortOrder&quot;: 29,
                  &quot;labelName&quot;: &quot;Description&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 28
              }, {
                  &quot;fieldName&quot;: &quot;care_instructions&quot;,
                  &quot;SortOrder&quot;: 30,
                  &quot;labelName&quot;: &quot;Care Instructions&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 29
              }, {
                  &quot;fieldName&quot;: &quot;target_gender&quot;,
                  &quot;SortOrder&quot;: 31,
                  &quot;labelName&quot;: &quot;Target Gender&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 30
              }, {
                  &quot;fieldName&quot;: &quot;edition&quot;,
                  &quot;SortOrder&quot;: 32,
                  &quot;labelName&quot;: &quot;Edition&quot;,
                  &quot;defaultValue&quot;: &quot;&quot;,
                  &quot;columnIndex&quot;: 31
              }]
          }]
      }
  }'
  --drop table #AmazonTemplateInfo
  ;with FieldInfo as (
  select Template,[Group],GroupSort,BackgroundColor,FieldName,FieldSort,LabelName,DefaultValue,ColumnIndex from OPENJSON(@jstring,'$.FieldInfo')
          With (
              Template varchar(50)
              ,Groups nvarchar(max) as json
          ) as Template
      cross apply openjson (Template.Groups)
          with (
              [Group] varchar(50)
              ,BackgroundColor varchar(6)
              ,GroupSort int '$.SortOrder'
              ,Fields nvarchar(max) as json
          ) Groups
      cross apply openjson(Groups.Fields)
          with (
              fieldName varchar(50)
              ,FieldSort int '$.SortOrder'
              ,labelName varchar(50)
              ,defaultValue varchar(max)
              ,columnIndex int
          ) Fields
  )
  select * from FieldInfo
  </code></pre>
  <p>This generates a table in the form</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Template</th>
  <th>Group</th>
  <th>GroupSort</th>
  <th>BackgroundColor</th>
  <th>FieldName</th>
  <th>FieldSort</th>
  <th>LabelName</th>
  <th>DefaultValue</th>
  <th>ColumnIndex</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Babies</td>
  <td>Required</td>
  <td>1</td>
  <td>FCD5B4</td>
  <td>feed_product_type</td>
  <td>1</td>
  <td>Product Type</td>
  <td></td>
  <td>0</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I need to create the same functionality in MariaDB v10. I've tried using <code>JSON_EXTRACT</code> and the LATERAL statement for my cross applies, but nothing is working. <code>JSON_EXTRACT</code> seems to just create a JSON string from the selected columns.</p>
  <p>Is this pointless in MariaDB? Is there any way to do this?</p>