1

My ultimate goal is I'm building a JSON_OBJECT() select statement that's collecting just a couple values from a stored json value.

My current iteration of the select statement: 

SELECT
  JSON_OBJECT(
      'id', document->>'$.id',
      'slug', document->>'$.slug',
      'title', document->>'$.title',
      'date_published', document->>'$.date_published',
      'toc', (SELECT JSON_OBJECT('title', document->>'$.toc[*][*].title') WHERE document->>'$.id' = document->>'$.id' )
  ) as document
FROM documents
WHERE 
  `type` = "item" AND
  NOW() > date_published;

where an example item document might be:

{
  "id": "d434089d-33ac-11e8-af1c-005056a40c60",
  "toc": [
    [
      {
        "id": "d4e1e442-57a9-11e8-af1c-005056a40c60",
        "slug": "0-intro",
        "chunk": 0,
        "index": 0,
        "pages": [],
        "title": "Intro",
        "urlsuffix": ""
      },
      {
        "id": "d4e1ef07-57a9-11e8-af1c-005056a40c60",
        "slug": "1-beginning",
        "chunk": 0,
        "index": 1,
        "pages": [
          {
            "id": "d4e1f726-57a9-11e8-af1c-005056a40c60",
            "slug": "2-nested-example",
            "chunk": 0,
            "index": 2,
            "pages": [],
            "title": "Nested Example",
            "urlsuffix": ""
          },
          {
            "id": "d4e1fee4-57a9-11e8-af1c-005056a40c60",
            "slug": "3-three-layers-deep",
            "chunk": 0,
            "index": 3,
            "pages": [
              {
                "id": "d4e2065e-57a9-11e8-af1c-005056a40c60",
                "slug": "4-four-in",
                "chunk": 0,
                "index": 4,
                "pages": [],
                "title": "Four In",
                "urlsuffix": ""
              },
              {
                "id": "d4e20e47-57a9-11e8-af1c-005056a40c60",
                "slug": "5-maximum-nesting",
                "chunk": 0,
                "index": 5,
                "pages": [],
                "title": "This is likely the maximum amount of Nesting",
                "urlsuffix": ""
              }
            ],
            "title": "Three Layers Deep",
            "urlsuffix": ""
          },
          {
            "id": "d4e2168b-57a9-11e8-af1c-005056a40c60",
            "slug": "6-follow-up-chapter",
            "chunk": 0,
            "index": 6,
            "pages": [],
            "title": "Follow-up Chapter",
            "urlsuffix": ""
          }
        ],
        "title": "The Beginning",
        "urlsuffix": ""
      }
    ]
  ],
  "slug": "an-item",
  "tags": [
    "test-item"
  ],
  "type": "item",
  "title": "An Item",
  "download": null,
  "date_added": "1522342602",
  "page_count": null,
  "description": "",
  "date_published": "1522238400"
}

And this is what I'm attempting to accomplish as a result record of the select statement (for the example document):

{
  "id": "d434089d-33ac-11e8-af1c-005056a40c60",
  "slug": "an-item",
  "title": "An Item",
  "date_published": "1522238400",
  "toc": [
    {
      "id": "d4e1e442-57a9-11e8-af1c-005056a40c60",
      "slug": "0-intro",
      "title": "Intro",
      "urlsuffix": ""
    },
    {
      "id": "d4e1ef07-57a9-11e8-af1c-005056a40c60",
      "slug": "1-beginning",
      "title": "The Beginning",
      "urlsuffix": ""
    }
  ]
}

So basically, just an array of the top level table of contents items (depth[1])

I will concede to accomplish the logic in the application if I absolutely must, I just feel there's got to be a way to do this using the MySQL json functions.

Is there a way to ask for a maximum depth when selecting an object/array?

I am working with 5.7.22, so I do have the latest fancy json features (JSON_ARRAYAGG/JSON_OBJECTAGG, etc)

RedactedProfile
  • 2,748
  • 6
  • 32
  • 51
  • 1
    It's possible that a stored procedure could help, here is a [dbfiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f833648d485760cd0b17ba304c81fcd4) that can give you ideas. – wchiquito May 15 '18 at 19:38
  • @wchiquito holy crap this is excellent! I didn't even consider a stored procedure.. Also, inadvertently, you've helped answer a bunch of pressing question's I've had about stored procedures with this one example. Thank you! – RedactedProfile May 15 '18 at 22:47
  • What you doing is relatively easy and I have been doing such things since the JSON datatype became available. There is a JSON_DEPTH function (see https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-depth) that has some obscure rules and would probably not be what you want. If you have to use embedded arrays (I highly recommend NOT TO DO THAT), you can use JSON_TABLE to crawl the array. It is much simpler if you architect your data not to embed too much – Dave Stokes May 03 '19 at 17:27

0 Answers0